﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using mojoPortal.Business;

namespace ud_gSDMS.Code_Service
{
    public class gSDMS_BL
    {
        #region ud_gSDMS_CAT_HOSTING_PACKAGE

        #region "ud_gSDMS_CAT_HOSTING_PACKAGE_Lst................"
        public static DataTable ud_gSDMS_CAT_HOSTING_PACKAGE_Lst()
        {
            var db = new gSDMS_DataLinq();
            return Library.LINQToDataTable(db,
                                           db.ud_gSDMS_CAT_HOSTING_PACKAGEs.Where(j => j.Active == true).OrderBy(
                                               j => j.Name));
        }
        #endregion

        #region "ud_gSDMS_CAT_HOSTING_PACKAGE_Lst_AddSize................"
        public static DataTable ud_gSDMS_CAT_HOSTING_PACKAGE_Lst_AddSize()
        {
            var db = new gSDMS_DataLinq();
            var query = from j in db.ud_gSDMS_CAT_HOSTING_PACKAGEs
                        where j.Active == true
                        orderby j.Name
                        select new
                                   {
                                       j.CAT_Hosting_ID,
                                       Name2 = (j.Name + " ...(" + j.Size.Value.ToString() + "Mb)"),
                                       j.Size,
                                       j.Description,
                                       j.Price
                                   };

            return Library.LINQToDataTable(db, query);
        }
        #endregion

        #region "ud_gSDMS_CAT_HOSTING_PACKAGE_Get(ID)................"
        public static ud_gSDMS_CAT_HOSTING_PACKAGE ud_gSDMS_CAT_HOSTING_PACKAGE_Get(string id)
        {
            int intId = string.IsNullOrEmpty(id) ? -1 : Convert.ToInt32(id);
            var db = new gSDMS_DataLinq();
            var o = db.ud_gSDMS_CAT_HOSTING_PACKAGEs.Where(j => j.CAT_Hosting_ID == intId).SingleOrDefault();
            return o;
        }
        #endregion

        #region "ud_gSDMS_CAT_HOSTING_PACKAGE_Delete................"
        public static void ud_gSDMS_CAT_HOSTING_PACKAGE_Del(string id)
        {
            int intId = string.IsNullOrEmpty(id) ? -1 : Convert.ToInt32(id);
            var db = new gSDMS_DataLinq();
            var o = db.ud_gSDMS_CAT_HOSTING_PACKAGEs.Where(j => j.CAT_Hosting_ID == intId).SingleOrDefault();
            if (o != null)
            {
                db.ud_gSDMS_CAT_HOSTING_PACKAGEs.DeleteOnSubmit(o);
                db.SubmitChanges();
            }
            return;
        }
        #endregion

        #region "ud_gSDMS_CAT_HOSTING_PACKAGE_Update................"
        public static void ud_gSDMS_CAT_HOSTING_PACKAGE_Upd(bool isInsert, string id, string name, string size,
                                                            string price, string description, bool active)
        {
            var db = new gSDMS_DataLinq();
            ud_gSDMS_CAT_HOSTING_PACKAGE o;

            if (isInsert)
            {
                o = new ud_gSDMS_CAT_HOSTING_PACKAGE();
            }
            else
            {
                int intId = Convert.ToInt32(id);
                o = db.ud_gSDMS_CAT_HOSTING_PACKAGEs.Where(j => j.CAT_Hosting_ID == intId).SingleOrDefault();
            }

            o.Name = name;
            if (!string.IsNullOrEmpty(size))
                o.Size = Convert.ToInt32(size);
            if (!string.IsNullOrEmpty(price))
                o.Price = Convert.ToDouble(price);
            o.Description = description;
            o.Active = active;

            if (isInsert)
            {
                db.ud_gSDMS_CAT_HOSTING_PACKAGEs.InsertOnSubmit(o);
            }
            db.SubmitChanges();
        }
        #endregion

        #region "Ham Tim kiem CAT_Hosting_Package............"
        public static DataTable ud_gSDMS_CAT_HOSTING_PACKAGE_Search(string name, bool active)
        {
            var db = new gSDMS_DataLinq();
            var data = from j in db.ud_gSDMS_CAT_HOSTING_PACKAGEs
                       where (name == "" ? true : j.Name.ToLower().Contains(name.ToLower()))
                             && j.Active == active
                       orderby j.Name ascending
                       select j;
            return Library.LINQToDataTable(db, data);
        }
        #endregion 

        #endregion  

        #region ud_gSDMS_CONTRACT
        #region "Ham tim kiem thong tin Contract.............."
        public static DataTable ud_gSDMS_CONTRACT_Search(string fromDate, string toDate, string code, string name,
                                                         string status, string accountManager, bool active)
        {
            var mindate = Convert.ToDateTime(System.Configuration.ConfigurationManager.AppSettings["MinDay"]);
            var p_fromdate = string.IsNullOrEmpty(fromDate) ? mindate : Convert.ToDateTime(fromDate + " 0:0:0");
            var p_todate = string.IsNullOrEmpty(toDate) ? DateTime.Now : Convert.ToDateTime(toDate + " 23:59:59");

            var db = new gSDMS_DataLinq();
            object data;

            data = from j in db.ud_gSDMS_CONTRACTs
                   where
                       (p_fromdate <= j.Contract_Date && j.Contract_Date <= p_todate)
                       && (string.IsNullOrEmpty(code) ? true : j.Contract_Code.Contains(code))
                       && (string.IsNullOrEmpty(name) ? true : j.Contract_Name.Contains(name))
                       //&& j.ud_gSDMS_PROJECTs.Count == 0
                       && (string.IsNullOrEmpty(status)
                               ? true
                               : (j.CAT_Contract_Status_ID.ToString() == status))
                       &&
                       (string.IsNullOrEmpty(accountManager)
                            ? true
                            : (j.mp_User.UserID == Convert.ToInt32(accountManager)))
                       && j.Active == active
                   orderby j.Contract_Date descending
                   select new
                              {
                                  j.Contract_ID,
                                  j.Contract_Code,
                                  j.Contract_Name,
                                  j.Contract_Date,
                                  Account_Manager = j.mp_User.LoginName,
                                  Assigned_To = j.mp_User1.LoginName,
                                  Status = j.ud_gSDMS_CAT_CONTRACT_STATUS.Name
                              };

            return Library.LINQToDataTable(db, data);
        }
        #endregion

        #region "Ham lay Contract_ID tu dong............."
        public static ud_gSDMS_CONTRACT ud_gSDMS_CONTRACT_Get(string id)
        {
            int intId = string.IsNullOrEmpty(id) ? -1 : Convert.ToInt32(id);
            var db = new gSDMS_DataLinq();
            var o = db.ud_gSDMS_CONTRACTs.Where(j => j.Contract_ID == intId).SingleOrDefault();
            return o;
        }

        public static ud_gSDMS_CONTRACT ud_gSDMS_CONTRACT_Get(string id, string code)
        {
            int intId = string.IsNullOrEmpty(id) ? -1 : Convert.ToInt32(id);
            var db = new gSDMS_DataLinq();
            var o = (from j in db.ud_gSDMS_CONTRACTs
                     where ((string.IsNullOrEmpty(id) ? true : j.Contract_ID == intId
                 && string.IsNullOrEmpty(code) ? true : j.Contract_Code == code))
                     select j).FirstOrDefault();

            return o;
        }
        #endregion


        #region "Ham update COntract........................"
        public static void ud_gSDMS_CONTRACT_Upd(ref string contract_ID, string contract_Code, string contract_Name,
                                                 string contract_Date, string cat_Product_Package_ID, string start_Date,
                                                 string end_Date, string resource, string customer_Code,
                                                 string customer_Name, string customer_Phone, string customer_Fax,
                                                 string customer_Email, string company_Address, string contact_Name_1, string contact_Email_1,
                                                 string contact_Phone_1, string contact_Name_2, string contact_Email_2,
                                                 string contact_Phone_2, string account_Manager, string assigned_To,
                                                 string notes, string cat_Contract_Status_ID, string createdBy,
                                                 string createdOn, bool active)
        {
            bool isInsert = string.IsNullOrEmpty(contract_ID);
            var db = new gSDMS_DataLinq();
            ud_gSDMS_CONTRACT o;
            if (isInsert)
            {
                o = new ud_gSDMS_CONTRACT();
            }
            else
            {
                int intContract_ID = Convert.ToInt32(contract_ID);
                o = db.ud_gSDMS_CONTRACTs.Where(j => j.Contract_ID == intContract_ID).SingleOrDefault();
            }
            if (!string.IsNullOrEmpty(contract_Code)) o.Contract_Code = contract_Code;
            if (!string.IsNullOrEmpty(contract_Name)) o.Contract_Name =  contract_Name;
            if (!string.IsNullOrEmpty(contract_Date)) o.Contract_Date =   Convert.ToDateTime(contract_Date);
            if (!string.IsNullOrEmpty(cat_Product_Package_ID)) o.Cat_Product_Package_ID = Convert.ToInt32(cat_Product_Package_ID);
            if (!string.IsNullOrEmpty(start_Date)) o.Start_Date = Convert.ToDateTime(start_Date);
            if (!string.IsNullOrEmpty(end_Date)) o.End_Date = Convert.ToDateTime(end_Date);
            if (!string.IsNullOrEmpty(resource)) o.Resource = Convert.ToInt32(resource);
            if (!string.IsNullOrEmpty(customer_Code)) o.Customer_Code = customer_Code;
            if (!string.IsNullOrEmpty(customer_Name)) o.Customer_Name = customer_Name;
            if (!string.IsNullOrEmpty(customer_Phone)) o.Customer_Phone = customer_Phone;
            if (!string.IsNullOrEmpty(customer_Fax)) o.Customer_Fax = customer_Fax;
            if (!string.IsNullOrEmpty(customer_Email)) o.Customer_Email = customer_Email;
            if (!string.IsNullOrEmpty(contact_Name_1)) o.Contact_Name_1 = contact_Name_1;
            if (!string.IsNullOrEmpty(contact_Email_1)) o.Contact_Email_1 = contact_Email_1;
            if (!string.IsNullOrEmpty(contact_Phone_1)) o.Contact_Phone_1 = contact_Phone_1;
            if (!string.IsNullOrEmpty(contact_Name_2)) o.Contact_Name_2 = contact_Name_2;
            if (!string.IsNullOrEmpty(contact_Email_2)) o.Contact_Email_2 = contact_Email_2;
            if (!string.IsNullOrEmpty(contact_Phone_2)) o.Contact_Phone_2 = contact_Phone_2;
            if (!string.IsNullOrEmpty(account_Manager)) o.Account_Manager = Convert.ToInt32(account_Manager);
            if (!string.IsNullOrEmpty(assigned_To)) o.Assigned_To = Convert.ToInt32(assigned_To);
            if (!string.IsNullOrEmpty(notes)) o.Notes = notes;
            if (!string.IsNullOrEmpty(cat_Contract_Status_ID)) o.CAT_Contract_Status_ID = Convert.ToInt32(cat_Contract_Status_ID);
            if (!string.IsNullOrEmpty(company_Address)) o.Customer_Address = company_Address;
            if (!string.IsNullOrEmpty(createdBy)) o.CreatedBy = Convert.ToInt32(createdBy);
            if (!string.IsNullOrEmpty(createdOn)) o.CreatedOn = Convert.ToDateTime(createdOn);
            o.Contract_File_ID = -1;

            o.Active = active;

            if (isInsert)
            {
                db.ud_gSDMS_CONTRACTs.InsertOnSubmit(o);
            }
            db.SubmitChanges();
            contract_ID = o.Contract_ID.ToString();
        }
        #endregion

        #region "Ham delete Contract.........."
        public static void ud_gSDMS_CONTRACT_Del(string id, bool isPermanent)
        {
            int intId = string.IsNullOrEmpty(id) ? -1 : Convert.ToInt32(id);
            var db = new gSDMS_DataLinq();
            var o = db.ud_gSDMS_CONTRACTs.Where(j => j.Contract_ID == intId).SingleOrDefault();
            if (o != null)
            {
                if (isPermanent)
                    db.ud_gSDMS_CONTRACTs.DeleteOnSubmit(o);
                else
                    o.Active = false;
                db.SubmitChanges();
            }
            return;
        }
        #endregion

        #region "Ham tu dong phat sinh Contract_AutoGencode.................."
        public static string ud_gSDMS_CONTRACT_AutoGenCode()
        {
            string code;
            int year = DateTime.Now.Year;
            var db = new gSDMS_DataLinq();
            var codeCount = (from j in db.ud_gSDMS_CONTRACTs
                             where j.CreatedOn.Value.Year == year
                             orderby j.CreatedOn descending
                             select j.Contract_Code).Take(1).SingleOrDefault();
            if (codeCount != null)
            {
                codeCount = (Convert.ToInt32(codeCount.Substring(0, 3)) + 1).ToString().PadLeft(3, '0');
                code = string.Format("{0}/HĐCCDV-GSOFT{1}", codeCount, year);
            }
            else
            {
                code = string.Format("{0}/HĐCCDV-GSOFT{1}", "001", year);
            }

            return code;
        }
        #endregion
        #endregion

        #region ud_gSDMS_HOSTING_CONTRACT

        public static string ud_gSDMS_HOSTING_CONTRACT_AutoGenCode()
        {
            var db = new gSDMS_DataLinq();
            var codeCount = (from j in db.ud_gSDMS_HOSTING_CONTRACTs
                             where j.CreatedOn.Value.Year == DateTime.Now.Year
                             orderby j.CreatedOn descending
                             select j.Contract_Code).Take(1).SingleOrDefault();
            var code = "";
            if (codeCount != null)
            {
                codeCount = (Convert.ToInt32(codeCount.Substring(0, 3)) + 1).ToString().PadLeft(3, '0');
                code = string.Format("{0}/HĐCCDVHD-GSOFT{1}", codeCount, DateTime.Now.Year);
            }
            else
            {
                code = string.Format("{0}/HĐCCDVHD-GSOFT{1}", "001", DateTime.Now.Year);
            }
            return code;
        }

        public static ud_gSDMS_HOSTING_CONTRACT ud_gSDMS_HOSTING_CONTRACT_Get(string id)
        {
            var intId = string.IsNullOrEmpty(id) ? -1 : Convert.ToInt32(id);
            if (intId == -1) return null;
            var db = new gSDMS_DataLinq();
            var query = db.ud_gSDMS_HOSTING_CONTRACTs.Where(j => j.Hosting_Contract_ID == intId).SingleOrDefault();

            return query;
        }

        public static void ud_gSDMS_HOSTING_CONTRACT_Upd(ref string Hosting_Contract_ID, string Contract_Code,
                                                         string Company_Name, string Company_Address, string Admin_Name, string Admin_Phone,
                                                         string Admin_Email, string Note, string CreatedBy, DateTime CreatedOn,
                                                         bool Active, DataTable listHostPackage, DataTable listDomain)
        {
            bool isInsert = string.IsNullOrEmpty(Hosting_Contract_ID);

            var db = new gSDMS_DataLinq();
            ud_gSDMS_HOSTING_CONTRACT o;

            if (isInsert)
            {
                o = new ud_gSDMS_HOSTING_CONTRACT();
            }
            else
            {
                var intHosting_Contract_ID = Convert.ToInt32(Hosting_Contract_ID);
                o =
                    db.ud_gSDMS_HOSTING_CONTRACTs.Where(j => j.Hosting_Contract_ID == intHosting_Contract_ID).
                        SingleOrDefault();
            }

            if (!string.IsNullOrEmpty(Contract_Code)) o.Contract_Code = Contract_Code;
            if (!string.IsNullOrEmpty(Company_Name)) o.Company_Name = Company_Name;
            if (!string.IsNullOrEmpty(Admin_Name)) o.Admin_Name = Admin_Name;
            if (!string.IsNullOrEmpty(Admin_Phone)) o.Admin_Phone = Admin_Phone;
            if (!string.IsNullOrEmpty(Admin_Email)) o.Admin_Email = Admin_Email;
            if (!string.IsNullOrEmpty(CreatedBy)) o.CreatedBy = Convert.ToInt32(CreatedBy);
            if (CreatedOn != null) o.CreatedOn = Convert.ToDateTime(CreatedOn);
            if (!string.IsNullOrEmpty(Company_Address)) o.Company_Address = Company_Address;
            if (!string.IsNullOrEmpty(Note)) o.Note = Note;

            o.Active = Active;

            if (isInsert)
            {
                db.ud_gSDMS_HOSTING_CONTRACTs.InsertOnSubmit(o);
            }

            db.ud_gSDMS_HOSTING_CONTRACT_HOSTINGs.DeleteAllOnSubmit(o.ud_gSDMS_HOSTING_CONTRACT_HOSTINGs);
                //Clear to insert new data
            db.ud_gSDMS_HOSTING_CONTRACT_DOMAINs.DeleteAllOnSubmit(o.ud_gSDMS_HOSTING_CONTRACT_DOMAINs);
                //Clear to insert new data

            db.SubmitChanges();

            Hosting_Contract_ID = o.Hosting_Contract_ID.ToString();

            var dateInfo = new System.Globalization.DateTimeFormatInfo {ShortDatePattern = "dd/MM/yyyy"};

            if (listHostPackage != null)
                foreach (DataRow dataRow in listHostPackage.Rows)
                {
                    if (dataRow["Cat_Hosting_ID"].ToString() != "")
                    {
                        string id = null;
                        ud_gSDMS_HOSTING_CONTRACT_HOSTING_Upd(ref id, Hosting_Contract_ID,
                                                              dataRow["Cat_Hosting_ID"].ToString(),
                                                              Convert.ToDateTime(dataRow["Start_Date"], dateInfo),
                                                              Convert.ToDateTime(dataRow["End_Date"], dateInfo),
                                                              dataRow["Domain"].ToString(),
                                                              dataRow["Hosting_Content"].ToString(), true);
                    }
                }
            if (listDomain != null)
                foreach (DataRow dataRow in listDomain.Rows)
                {
                    if (dataRow["Domain_Name"].ToString() != "")
                    {
                        string id = null;
                        ud_gSDMS_HOSTING_CONTRACT_Domain_Upd(ref id, Hosting_Contract_ID,
                                                             dataRow["Domain_Name"].ToString(),
                                                             Convert.ToBoolean(dataRow["World_Domain"]),
                                                             Convert.ToBoolean(dataRow["VietNam_Domain"]),
                                                             Convert.ToDateTime(dataRow["Start_Date"], dateInfo),
                                                             Convert.ToDateTime(dataRow["End_Date"], dateInfo),
                                                             string.IsNullOrEmpty(dataRow["Price"].ToString()) ? 0 : Convert.ToDouble(dataRow["Price"]),
                                                             dataRow["Domain_Content"].ToString(),true);
                    }
                }
        }

        public static DataTable ud_gSDMS_HOSTING_CONTRACT_Lst()
        {
            var db = new gSDMS_DataLinq();
            var data = from j in db.ud_gSDMS_HOSTING_CONTRACTs
                       where j.Active == true
                       select j;
            return Library.LINQToDataTable(db, data);
        }
        public static bool ud_gSDMS_HOSTING_CONTRACT_Del_DeActive(string id)
        {

            var db = new gSDMS_DataLinq();
            var data = (from j in db.ud_gSDMS_HOSTING_CONTRACTs
                        where j.Active == true
                              && j.Hosting_Contract_ID.Equals(id)
                        select j).SingleOrDefault();
            if (data != null)
            {
                data.Active = false;
                db.SubmitChanges();
            }
            return true;
        }

        public static void ud_gSDMS_HOSTING_CONTRACT_Del(string id, bool isPermanent)
        {
            int intId = string.IsNullOrEmpty(id) ? -1 : Convert.ToInt32(id);
            var db = new gSDMS_DataLinq();
            var o = db.ud_gSDMS_HOSTING_CONTRACTs.Where(j => j.Hosting_Contract_ID == intId).SingleOrDefault();
            if (o != null)
            {
                if (isPermanent)
                    db.ud_gSDMS_HOSTING_CONTRACTs.DeleteOnSubmit(o);
                else
                    o.Active = false;
                db.SubmitChanges();
            }
            return;
        }

        public static DataTable ud_gSDMS_HOSTING_CONTRACT_Search(string Contract_Code, string Company_Name,
                                                                 string CAT_Hosting_ID, string Domain_Name, bool active)
        {
            var cat_hosting_id = string.IsNullOrEmpty(CAT_Hosting_ID) ? -1 : Convert.ToInt32(CAT_Hosting_ID);

            var db = new gSDMS_DataLinq();
            var queryable1 = from j in db.ud_gSDMS_HOSTING_CONTRACTs
                             where
                                 (Contract_Code == "" ? true : j.Contract_Code.Contains(Contract_Code))
                                 && (Company_Name == "" ? true : j.Company_Name.Contains(Company_Name))
                                 &&
                                 (cat_hosting_id == -1
                                      ? true
                                      : j.ud_gSDMS_HOSTING_CONTRACT_HOSTINGs.Where(
                                          i => i.Cat_Hosting_ID == cat_hosting_id).Count() > 0)
                                 &&
                                 (Domain_Name == ""
                                      ? true
                                      : j.ud_gSDMS_HOSTING_CONTRACT_DOMAINs.Where(
                                          i => i.Domain_Name.Contains(Domain_Name)).Count() > 0)
                                 && j.Active == active
                             orderby j.Contract_Code , j.CreatedOn descending
                             select j;

            var data = Library.LINQToDataTable(db, queryable1);
            data.Columns[0].Unique = false;

            if (data.Rows.Count == 0) return data;

            #region Addcolumn

            data.Columns.Add(new DataColumn("Hosting_Contract_Hosting_ID"));
            data.Columns.Add(new DataColumn("PackageName"));
            data.Columns.Add(new DataColumn("Size"));
            data.Columns.Add(new DataColumn("Start_Date", typeof (DateTime)));
            data.Columns.Add(new DataColumn("End_Date", typeof (DateTime)));

            data.Columns.Add(new DataColumn("Hosting_Contract_Domain_ID"));
            data.Columns.Add(new DataColumn("World_Domain"));
            data.Columns.Add(new DataColumn("VietNam_Domain"));
            data.Columns.Add(new DataColumn("Start_Date_DM", typeof (DateTime)));
            data.Columns.Add(new DataColumn("End_Date_DM", typeof (DateTime)));

            #endregion

            var codeOld = "";
            var count = data.Rows.Count;
            DataTable hostLst = new DataTable(), domainLst = new DataTable();
            var count_hostLst = 0;
            var count_domainLst = 0;
            var index_hostLst = 0;
            var index_domainLst = 0;

            for (var i = 0; i < count ;)
            {
                index_domainLst = index_hostLst = 0;
                bool isIncrease = true;
                
                var row = data.Rows[i];
                //var codeNew = row["Contract_Code"].ToString();
                hostLst = Library.LINQToDataTable(db,
                                                  db.ud_gSDMS_HOSTING_CONTRACT_HOSTINGs.Where(
                                                      j =>
                                                      j.Hosting_Contract_ID ==
                                                      Convert.ToInt32(row["Hosting_Contract_ID"])).Select(
                                                          j => new
                                                                   {
                                                                       j.Hosting_Contract_Hosting_ID,
                                                                       PackageName = j.ud_gSDMS_CAT_HOSTING_PACKAGE.Name,
                                                                       j.ud_gSDMS_CAT_HOSTING_PACKAGE.Size,
                                                                       j.Start_Date,
                                                                       j.End_Date
                                                                   }));
                domainLst = Library.LINQToDataTable(db,
                                                    db.ud_gSDMS_HOSTING_CONTRACT_DOMAINs.Where(
                                                        j =>
                                                        j.Hosting_Contract_ID ==
                                                        Convert.ToInt32(row["Hosting_Contract_ID"])).Select(
                                                            j => new
                                                                     {
                                                                         j.Hosting_Contract_Domain_ID,
                                                                         World_Domain =
                                                                     j.World_Domain ? j.Domain_Name : "",
                                                                         VietNam_Domain =
                                                                     j.VietNam_Domain ? j.Domain_Name : "",
                                                                         Start_Date_DM = j.Start_Date,
                                                                         End_Date_DM = j.End_Date
                                                                     }));
                count_hostLst = hostLst.Rows.Count;
                count_domainLst = domainLst.Rows.Count;
                var maxRowCount = Math.Max(count_hostLst, count_domainLst);

                for (var j = 0; j < maxRowCount; j++)
                {
                    if (j > 0) //neu la dong thu 2 thi insert them dong moi vao
                    {
                        var tem = data.NewRow();
                        for (int k = 0; k < 9; k++) tem[k] = row[k];
                        row = tem;
                        data.Rows.InsertAt(row, i);
                        count = data.Rows.Count;
                    }

                    if (j < count_hostLst)
                    {
                        var r = hostLst.Rows[index_hostLst];
                        row["Hosting_Contract_Hosting_ID"] = r["Hosting_Contract_Hosting_ID"];
                        row["PackageName"] = r["PackageName"];
                        row["Size"] = r["Size"];
                        row["Start_Date"] = r["Start_Date"];
                        // == null ? null : Convert.ToDateTime(r["Start_Date"]).ToString("dd/MM/yyyy");
                        row["End_Date"] = r["End_Date"];
                        //== null ? null : Convert.ToDateTime(r["End_Date"]).ToString("dd/MM/yyyy");
                        index_hostLst++;
                    }
                    if (j < count_domainLst)
                    {
                        var r = domainLst.Rows[index_domainLst];
                        row["Hosting_Contract_Domain_ID"] = r["Hosting_Contract_Domain_ID"];
                        row["World_Domain"] = r["World_Domain"];
                        row["VietNam_Domain"] = r["VietNam_Domain"];
                        row["Start_Date_DM"] = r["Start_Date_DM"];
                        // == null ? null : Convert.ToDateTime(r["Start_Date_DM"]).ToString("dd/MM/yyyy");
                        row["End_Date_DM"] = r["End_Date_DM"];
                        //== null ? null : Convert.ToDateTime(r["End_Date_DM"]).ToString("dd/MM/yyyy");
                        index_domainLst++;
                    }

                    i = i + 1; //Dòng nay cực kỳ quan trọng
                    isIncrease = false;
                }

                if (isIncrease) i = i + 1;
            }
            return data;
        }

        public static DataTable ud_gSDMS_HOSTING_CONTRACT_Extend_Search(DateTime fromDate, DateTime toDate,
                                                                        string Contract_Code, string Company_Name,
                                                                        string CAT_Hosting_ID, string Domain_Name,
                                                                        bool outOfDate, bool active)
        {
            var now = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59);
            var cat_hosting_id = string.IsNullOrEmpty(CAT_Hosting_ID) ? -1 : Convert.ToInt32(CAT_Hosting_ID);
            var db = new gSDMS_DataLinq();
            //
            var queryable1 = from j in db.ud_gSDMS_HOSTING_CONTRACTs
                             where
                                 (string.IsNullOrEmpty(Contract_Code) ? true : j.Contract_Code.Contains(Contract_Code))
                                 && (string.IsNullOrEmpty(Company_Name) ? true : j.Company_Name.Contains(Company_Name))
                                 &&
                                 (cat_hosting_id == -1
                                      ? true
                                      : j.ud_gSDMS_HOSTING_CONTRACT_HOSTINGs.Where(
                                          i => i.Cat_Hosting_ID == cat_hosting_id).Count() > 0)
                                 &&
                                 (string.IsNullOrEmpty(Domain_Name)
                                      ? true
                                      : j.ud_gSDMS_HOSTING_CONTRACT_DOMAINs.Where(
                                          i => i.Domain_Name.Contains(Domain_Name)).Count() > 0)
                                 //To do
                                 &&
                                 (j.ud_gSDMS_HOSTING_CONTRACT_DOMAINs.Count(
                                     i =>
                                     (!outOfDate ? true : (i.End_Date <= now)) && fromDate <= i.End_Date &&
                                     i.End_Date <= toDate) > 0 ||
                                  j.ud_gSDMS_HOSTING_CONTRACT_HOSTINGs.Count(
                                      i =>
                                      (!outOfDate ? true : (i.End_Date <= now)) && fromDate <= i.End_Date &&
                                      i.End_Date <= toDate) > 0)

                                 && j.Active == active
                             orderby j.Contract_Code, j.CreatedOn descending
                             select j;

            var data = Library.LINQToDataTable(db, queryable1);
            data.Columns[0].Unique = false;

            if (data.Rows.Count == 0) return data;

            #region Addcolumn

            data.Columns.Add(new DataColumn("Hosting_Contract_Hosting_ID"));
            data.Columns.Add(new DataColumn("PackageName"));
            data.Columns.Add(new DataColumn("Size"));
            data.Columns.Add(new DataColumn("Start_Date", typeof(DateTime)));
            data.Columns.Add(new DataColumn("End_Date", typeof(DateTime)));

            data.Columns.Add(new DataColumn("Hosting_Contract_Domain_ID"));
            data.Columns.Add(new DataColumn("World_Domain"));
            data.Columns.Add(new DataColumn("VietNam_Domain"));
            data.Columns.Add(new DataColumn("Start_Date_DM", typeof(DateTime)));
            data.Columns.Add(new DataColumn("End_Date_DM", typeof(DateTime)));

            #endregion

            var codeOld = "";
            var count = data.Rows.Count;
            DataTable hostLst = new DataTable(), domainLst = new DataTable();
            var count_hostLst = 0;
            var count_domainLst = 0;
            var index_hostLst = 0;
            var index_domainLst = 0;

            for (var i = 0; i < count; )
            {
                var row = data.Rows[i];
                //var codeNew = row["Contract_Code"].ToString();
                hostLst = Library.LINQToDataTable(db,
                                                  db.ud_gSDMS_HOSTING_CONTRACT_HOSTINGs.Where(
                                                      j =>
                                                      j.Hosting_Contract_ID ==
                                                      Convert.ToInt32(row["Hosting_Contract_ID"])).Select(
                                                          j => new
                                                          {
                                                              j.Hosting_Contract_Hosting_ID,
                                                              PackageName = j.ud_gSDMS_CAT_HOSTING_PACKAGE.Name,
                                                              j.ud_gSDMS_CAT_HOSTING_PACKAGE.Size,
                                                              j.Start_Date,
                                                              j.End_Date
                                                          }));
                domainLst = Library.LINQToDataTable(db,
                                                    db.ud_gSDMS_HOSTING_CONTRACT_DOMAINs.Where(
                                                        j =>
                                                        j.Hosting_Contract_ID ==
                                                        Convert.ToInt32(row["Hosting_Contract_ID"])).Select(
                                                            j => new
                                                            {
                                                                j.Hosting_Contract_Domain_ID,
                                                                World_Domain =
                                                            j.World_Domain ? j.Domain_Name : "",
                                                                VietNam_Domain =
                                                            j.VietNam_Domain ? j.Domain_Name : "",
                                                                Start_Date_DM = j.Start_Date,
                                                                End_Date_DM = j.End_Date
                                                            }));
                count_hostLst = hostLst.Rows.Count;
                count_domainLst = domainLst.Rows.Count;
                var maxRowCount = Math.Max(count_hostLst, count_domainLst);
                for (var j = 0; j < maxRowCount; j++)
                {
                    if (j > 0) //neu la dong thu 2 thi insert them dong moi vao
                    {
                        var tem = data.NewRow();
                        for (int k = 0; k < 9; k++) tem[k] = row[k];
                        row = tem;
                        data.Rows.InsertAt(row, i);
                        count = data.Rows.Count;
                    }
                    if (j < count_hostLst)
                    {
                        var r = hostLst.Rows[index_hostLst];
                        row["Hosting_Contract_Hosting_ID"] = r["Hosting_Contract_Hosting_ID"];
                        row["PackageName"] = r["PackageName"];
                        row["Size"] = r["Size"];
                        row["Start_Date"] = r["Start_Date"];
                        // == null ? null : Convert.ToDateTime(r["Start_Date"]).ToString("dd/MM/yyyy");
                        row["End_Date"] = r["End_Date"];
                        //== null ? null : Convert.ToDateTime(r["End_Date"]).ToString("dd/MM/yyyy");
                        index_hostLst++;
                    }
                    if (j < count_domainLst)
                    {
                        var r = domainLst.Rows[index_domainLst];
                        row["Hosting_Contract_Domain_ID"] = r["Hosting_Contract_Domain_ID"];
                        row["World_Domain"] = r["World_Domain"];
                        row["VietNam_Domain"] = r["VietNam_Domain"];
                        row["Start_Date_DM"] = r["Start_Date_DM"];
                        // == null ? null : Convert.ToDateTime(r["Start_Date_DM"]).ToString("dd/MM/yyyy");
                        row["End_Date_DM"] = r["End_Date_DM"];
                        //== null ? null : Convert.ToDateTime(r["End_Date_DM"]).ToString("dd/MM/yyyy");
                        index_domainLst++;
                    }

                    i = i + 1; //Dòng nay cực kỳ quan trọng
                }
            }
            return data;
        }

        public static DataTable ud_gSDMS_HOSTING_CONTRACT_Report_Search(string from_Date, string to_Date,
                                                                        string Contract_Code, string Company_Name,
                                                                        bool Is_Hosting, bool Is_Domain,
                                                                        bool outOfDate, bool active, bool Can_Renew)
        {
            var now = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59);
            var mindate = Convert.ToDateTime(System.Configuration.ConfigurationManager.AppSettings["MinDay"]);
            var fromDate = from_Date == null ? mindate : Convert.ToDateTime(from_Date);
            var toDate = to_Date == null ? now.Date : Convert.ToDateTime(to_Date);

            var db = new gSDMS_DataLinq();
            //
            var queryable1 = from j in db.ud_gSDMS_HOSTING_CONTRACTs
                             where
                                 (Contract_Code == "" ? true : j.Contract_Code.Contains(Contract_Code))
                                 && (Company_Name == "" ? true : j.Company_Name.Contains(Company_Name))
                          
                             orderby j.Contract_Code , j.CreatedOn descending
                             select j;

            var data = Library.LINQToDataTable(db, queryable1);
            data.Columns[0].Unique = false;

            if (data.Rows.Count == 0) return data;

            #region Addcolumn

            data.Columns.Add(new DataColumn("Hosting_Contract_Hosting_ID"));
            data.Columns.Add(new DataColumn("PackageName"));
            data.Columns.Add(new DataColumn("Size"));
            data.Columns.Add(new DataColumn("Start_Date", typeof (DateTime)));
            data.Columns.Add(new DataColumn("End_Date", typeof (DateTime)));

            data.Columns.Add(new DataColumn("Hosting_Contract_Domain_ID"));
            data.Columns.Add(new DataColumn("World_Domain"));
            data.Columns.Add(new DataColumn("VietNam_Domain"));
            data.Columns.Add(new DataColumn("Start_Date_DM", typeof (DateTime)));
            data.Columns.Add(new DataColumn("End_Date_DM", typeof (DateTime)));

            data.Columns.Add(new DataColumn("Hosting_Active", typeof(bool)));
            data.Columns["Hosting_Active"].DefaultValue = false;

            data.Columns.Add(new DataColumn("Domain_Active", typeof(bool)));
            data.Columns["Domain_Active"].DefaultValue = false;

            #endregion

            var codeOld = "";
            var count = data.Rows.Count;
            DataTable hostLst = new DataTable(), domainLst = new DataTable();
            var count_hostLst = 0;
            var count_domainLst = 0;
            var index_hostLst = 0;
            var index_domainLst = 0;

            for (var i = 0; i < count;)
            {
                var row = data.Rows[i];
                bool isIncrease = true;
                index_domainLst = index_hostLst = 0;
                //var codeNew = row["Contract_Code"].ToString();
                hostLst = Library.LINQToDataTable(db,
                                                  db.ud_gSDMS_HOSTING_CONTRACT_HOSTINGs.Where(
                                                      j =>
                                                      (j.Hosting_Contract_ID ==
                                                      Convert.ToInt32(row["Hosting_Contract_ID"]) 
                                                      && (from_Date == null ? true : fromDate.Date <= j.Start_Date.Value.Date
                                                      && to_Date == null ? true : j.Start_Date.Value.Date <= toDate.Date)
                                                      && (outOfDate ? (j.End_Date.Value.Date < DateTime.Today.Date) :  (j.End_Date.Value.Date >= DateTime.Today.Date))
                                                      && (Can_Renew ? (j.End_Date.Value.Date > DateTime.Today.Date &&
                                                      j.End_Date.Value.AddDays(-7) < DateTime.Today.Date) : true)
                                                      && (j.Active == active) && (Is_Hosting))
                                                      ).Select(
                                                          j => new
                                                                   {
                                                                       j.Hosting_Contract_Hosting_ID,
                                                                       PackageName = j.ud_gSDMS_CAT_HOSTING_PACKAGE.Name,
                                                                       j.ud_gSDMS_CAT_HOSTING_PACKAGE.Size,
                                                                       j.Start_Date,
                                                                       j.End_Date,
                                                                       j.Active
                                                                   }));
                domainLst = Library.LINQToDataTable(db,
                                                    db.ud_gSDMS_HOSTING_CONTRACT_DOMAINs.Where(
                                                        j =>
                                                        (j.Hosting_Contract_ID ==
                                                        Convert.ToInt32(row["Hosting_Contract_ID"])
                                                        && (from_Date == null ? true : fromDate.Date <= j.Start_Date.Value.Date
                                                      && to_Date == null ? true : j.Start_Date.Value.Date <= toDate.Date)
                                                      && (outOfDate ? (j.End_Date.Value.Date < DateTime.Today.Date) : (j.End_Date.Value.Date >= DateTime.Today.Date))
                                                      && (Can_Renew ? (j.End_Date.Value.Date > DateTime.Today.Date &&
                                                      j.End_Date.Value.AddDays(-7) < DateTime.Today.Date) : true)
                                                      && (j.Active == active) && (Is_Domain))).Select(
                                                            j => new
                                                                     {
                                                                         j.Hosting_Contract_Domain_ID,
                                                                         World_Domain =
                                                                     j.World_Domain ? j.Domain_Name : "",
                                                                         VietNam_Domain =
                                                                     j.VietNam_Domain ? j.Domain_Name : "",
                                                                         Start_Date_DM = j.Start_Date,
                                                                         End_Date_DM = j.End_Date,
                                                                         Domain_Active = j.Active
                                                                     }));
                count_hostLst = hostLst.Rows.Count;
                count_domainLst = domainLst.Rows.Count;
                var maxRowCount = Math.Max(count_hostLst, count_domainLst);

                if (maxRowCount == 0)
                {
                    data.Rows.Remove(row);
                    count--;
                }
                else
                {
                    for (var j = 0; j < maxRowCount; j++)
                    {
                        if (j > 0) //neu la dong thu 2 thi insert them dong moi vao
                        {
                            var tem = data.NewRow();
                            for (int k = 0; k < 9; k++) tem[k] = row[k];
                            row = tem;
                            data.Rows.InsertAt(row, i);
                            count = data.Rows.Count;
                        }
                        if (j < count_hostLst)
                        {
                            var r = hostLst.Rows[index_hostLst];
                            row["Hosting_Contract_Hosting_ID"] = r["Hosting_Contract_Hosting_ID"];
                            row["PackageName"] = r["PackageName"];
                            row["Size"] = r["Size"];
                            row["Start_Date"] = r["Start_Date"];
                            // == null ? null : Convert.ToDateTime(r["Start_Date"]).ToString("dd/MM/yyyy");
                            row["End_Date"] = r["End_Date"];
                            //== null ? null : Convert.ToDateTime(r["End_Date"]).ToString("dd/MM/yyyy");
                            row["Hosting_Active"] = r["Active"];

                            index_hostLst++;
                        }
                        if (j < count_domainLst)
                        {
                            var r = domainLst.Rows[index_domainLst];
                            row["Hosting_Contract_Domain_ID"] = r["Hosting_Contract_Domain_ID"];
                            row["World_Domain"] = r["World_Domain"];
                            row["VietNam_Domain"] = r["VietNam_Domain"];
                            row["Start_Date_DM"] = r["Start_Date_DM"];
                            // == null ? null : Convert.ToDateTime(r["Start_Date_DM"]).ToString("dd/MM/yyyy");
                            row["End_Date_DM"] = r["End_Date_DM"];
                            //== null ? null : Convert.ToDateTime(r["End_Date_DM"]).ToString("dd/MM/yyyy");
                            row["Domain_Active"] = r["Domain_Active"];
                            index_domainLst++;
                        }


                        i = i + 1; //Dòng nay cực kỳ quan trọng
                        isIncrease = false;
                    }
                    if (isIncrease) i = i + 1;
                }
            }
            return data;
        }

        public static DataTable ud_gSDMS_HOSTING_CONTRACT_DOMAIN_Lst(string Hosting_Contract_Domain_ID,
                                                                     string Hosting_Contract_ID, bool active)
        {
            var db = new gSDMS_DataLinq();
            var data = from j in db.ud_gSDMS_HOSTING_CONTRACT_DOMAINs
                       where
                           (string.IsNullOrEmpty(Hosting_Contract_Domain_ID)
                                ? true
                                : j.Hosting_Contract_Domain_ID == Convert.ToInt32(Hosting_Contract_Domain_ID))
                           && string.IsNullOrEmpty(Hosting_Contract_ID)
                               ? true
                               : j.Hosting_Contract_ID == Convert.ToInt32(Hosting_Contract_ID)
                                 && j.Active == active
                       select j;
            return Library.LINQToDataTable(db, data);
        }

        public static void ud_gSDMS_HOSTING_CONTRACT_Domain_Upd(ref string Hosting_Contract_Domain_ID,
                                                                string Hosting_Contract_ID, string Domain_Name,
                                                                bool World_Domain, bool VietNam_Domain,
                                                                DateTime Start_Date, DateTime End_Date, double price, string Domain_Content, bool Active)
        {
            var isInsert = string.IsNullOrEmpty(Hosting_Contract_Domain_ID);

            var db = new gSDMS_DataLinq();
            ud_gSDMS_HOSTING_CONTRACT_DOMAIN o;

            if (isInsert)
            {
                o = new ud_gSDMS_HOSTING_CONTRACT_DOMAIN();
            }
            else
            {
                var intHosting_Contract_Domain_ID = Convert.ToInt32(Hosting_Contract_Domain_ID);
                o =
                    db.ud_gSDMS_HOSTING_CONTRACT_DOMAINs.Where(
                        j => j.Hosting_Contract_Domain_ID == intHosting_Contract_Domain_ID).SingleOrDefault();
            }

            o.Hosting_Contract_ID = Convert.ToInt32(Hosting_Contract_ID);
            o.Domain_Name = Domain_Name;
            o.World_Domain = World_Domain;
            o.VietNam_Domain = VietNam_Domain;
            o.Start_Date = Start_Date;
            o.End_Date = End_Date;
            o.Domain_Content = Domain_Content;
            o.Active = Active;
            o.Price = Convert.ToDouble(price);
            if (isInsert)
            {
                db.ud_gSDMS_HOSTING_CONTRACT_DOMAINs.InsertOnSubmit(o);
            }
            db.SubmitChanges();
            Hosting_Contract_Domain_ID = o.Hosting_Contract_Domain_ID.ToString();
        }

        public static DataTable ud_gSDMS_HOSTING_CONTRACT_HOSTING_Lst(string Hosting_Contract_Hosting_ID,
                                                                      string Hosting_Contract_ID, bool active)
        {
            var db = new gSDMS_DataLinq();
            var query1 = from j in db.ud_gSDMS_HOSTING_CONTRACT_HOSTINGs
                         where
                             (string.IsNullOrEmpty("Hosting_Contract_Hosting_ID")
                                  ? true
                                  : j.Hosting_Contract_Hosting_ID == Convert.ToInt32(Hosting_Contract_Hosting_ID))
                             && string.IsNullOrEmpty("Hosting_Contract_ID")
                                 ? true
                                 : j.Hosting_Contract_ID == Convert.ToInt32(Hosting_Contract_ID)
                                   && j.Active == active
                         select j;
            var query2 = from j in query1
                         join p in db.ud_gSDMS_CAT_HOSTING_PACKAGEs on j.Cat_Hosting_ID equals p.CAT_Hosting_ID into p1
                         from p2 in p1.DefaultIfEmpty()
                         select new
                                    {
                                        j.Hosting_Contract_Hosting_ID,
                                        j.Hosting_Contract_ID,
                                        j.Cat_Hosting_ID,
                                        j.Start_Date,
                                        j.End_Date,
                                        p2.Name,
                                        p2.Size,
                                        p2.Price,
                                        p2.Description
                                    };

            return Library.LINQToDataTable(db, query2);
        }

        public static void ud_gSDMS_HOSTING_CONTRACT_HOSTING_Upd(ref string Hosting_Contract_Hosting_ID,
                                                                 string Hosting_Contract_ID, string Cat_Hosting_ID,
                                                                 DateTime Start_Date, DateTime End_Date, string Domain, string Hosting_Content, bool Active)
        {
            bool isInsert = string.IsNullOrEmpty(Hosting_Contract_Hosting_ID);

            var db = new gSDMS_DataLinq();
            ud_gSDMS_HOSTING_CONTRACT_HOSTING o;

            if (isInsert)
            {
                o = new ud_gSDMS_HOSTING_CONTRACT_HOSTING();
            }
            else
            {
                var intHosting_Contract_Hosting_ID = Convert.ToInt32(Hosting_Contract_Hosting_ID);
                o =
                    db.ud_gSDMS_HOSTING_CONTRACT_HOSTINGs.Where(
                        j => j.Hosting_Contract_Hosting_ID == intHosting_Contract_Hosting_ID).SingleOrDefault();
            }

            o.Hosting_Contract_ID = Convert.ToInt32(Hosting_Contract_ID);
            o.Cat_Hosting_ID = Convert.ToInt32(Cat_Hosting_ID);
            o.Start_Date = Start_Date;
            o.End_Date = End_Date;
            o.Active = Active;
            o.Hosting_Content = Hosting_Content;
            o.Domain = Domain;

            if (isInsert)
            {
                db.ud_gSDMS_HOSTING_CONTRACT_HOSTINGs.InsertOnSubmit(o);
            }
            db.SubmitChanges();
            Hosting_Contract_Hosting_ID = o.Hosting_Contract_Hosting_ID.ToString();
        }

        public static void ud_gSDMS_HOSTING_CONTRACT_HOSTING_Del(string Hosting_Contract_Hosting_ID,
                                                                 string Hosting_Contract_ID)
        {
            var db = new gSDMS_DataLinq();
            var o = from j in db.ud_gSDMS_HOSTING_CONTRACT_HOSTINGs
                    where
                        (string.IsNullOrEmpty(Hosting_Contract_Hosting_ID)
                             ? true
                             : j.Hosting_Contract_Hosting_ID == Convert.ToInt32(Hosting_Contract_Hosting_ID))
                        &&
                        (string.IsNullOrEmpty(Hosting_Contract_ID)
                             ? true
                             : j.Hosting_Contract_ID == Convert.ToInt32(Hosting_Contract_ID))
                    select j;

            if (o.Count() <= 0) return;
            db.ud_gSDMS_HOSTING_CONTRACT_HOSTINGs.DeleteAllOnSubmit(o);
            db.SubmitChanges();
        }

        #endregion

        #region mpUser
        public static mp_User mp_User_get(string id, string userName)
        {
            var db = new gSDMS_DataLinq();
            var user = (from j in db.mp_Users
                        where
                            (string.IsNullOrEmpty(id) ? true : Convert.ToInt32(id) == j.UserID) &&
                            (string.IsNullOrEmpty(userName) ? true : userName == j.LoginName)
                        select j).SingleOrDefault();
            return user;
        }
        public static mp_User mp_User_getProjectLeader(string id)
        {
            var db = new gSDMS_DataLinq();
            int project_id = Convert.ToInt32(id);
            var query = (from j in db.mp_Users
                         join l in db.ud_gSDMS_PROJECTs on j.UserID equals l.Project_Leader
                         where l.Project_ID == project_id
                         select j).SingleOrDefault();
            return query;
        }

        public static mp_User mp_User_getProjectDir(string id)
        {
            var db = new gSDMS_DataLinq();
            int project_id = Convert.ToInt32(id);
            var query = (from j in db.mp_Users
                        join l in db.ud_gSDMS_PROJECTs on j.UserID equals l.Project_Director
                        where l.Project_ID == project_id
                        select j).SingleOrDefault();
            return query;
        }
        //public int getUser_ID()
        //{
        //    var db = new gSDMS_DataLinq();
        //    var user_id = (from j in db.mp_Users 
        //                   where ()
        //                  );
        //    return user_id;
        //}
        public static DataTable mp_User_Search(string UserID, string Name, string LoginName, string Email,
                                               string FirstName, string LastName, string roleID)
        {

            var roles = roleID.Split('|');

            var db = new gSDMS_DataLinq();
            var data = from u in db.mp_Users
                       where (string.IsNullOrEmpty(UserID) ? true : u.UserID == Convert.ToInt32(UserID))
                             && (string.IsNullOrEmpty(Name) ? true : u.Name.Contains(Name))
                             && (string.IsNullOrEmpty(LoginName) ? true : u.LoginName.Contains(LoginName))
                             && (string.IsNullOrEmpty(Email) ? true : u.Email.Contains(Email))
                             && (string.IsNullOrEmpty(FirstName) ? true : u.FirstName.Contains(FirstName))
                             && (string.IsNullOrEmpty(LastName) ? true : u.LastName.Contains(LastName))
                       select u;
            var mpUsers = from j in data where false select j;
            mpUsers = (from c in roles
                       select (from u in data
                               from ur in db.mp_UserRoles
                               from r in db.mp_Roles
                               where
                                   u.UserID == ur.UserID && ur.RoleID == r.RoleID &&
                                   (string.IsNullOrEmpty(c)
                                        ? true
                                        : r.RoleID == Convert.ToInt32(c))
                               select u)).Aggregate(mpUsers, (current, d) => current.Union(d));

            //var dataTable = Library.LINQToDataTable(db, mpUsers);
            var dataTable = Library.LINQToDataTable(db, mpUsers.OrderBy(j => j.LoginName));

            return dataTable;
        }

        static public DataTable mp_User_Lst()
        {
            var db = new gSDMS_DataLinq();
            var users = from c in db.mp_Users
                        select c;

            return Library.LINQToDataTable(db, users);
        }

        static public DataTable mp_User_get(int userID)
        {
            var db = new gSDMS_DataLinq();
            var temp = from c in db.mp_Users
                       where c.UserID == userID
                       select c;

            return Library.LINQToDataTable(db, temp);
        }
        #endregion

        #region ud_gSDMS_PROJECT

        public static void ud_gSDMS_PROJECT_Upd(ref string Project_ID, string Contract_ID, string Code, string Name,
                                                string Kickoff_Date, string End_Date, string Resource,
                                                string Project_Leader, string Project_Director, string CAT_Project_Step_ID,
                                                string CAT_Project_Plan_Status_ID, string CreatedBy, string CreatedOn,
                                                bool Active)
        {
            bool isInsert = string.IsNullOrEmpty(Project_ID);

            var db = new gSDMS_DataLinq();
            ud_gSDMS_PROJECT o;
            

            if (isInsert)
            {
                o = new ud_gSDMS_PROJECT();
            }
            else
            {
                var intProject_ID = Convert.ToInt32(Project_ID);
                o = db.ud_gSDMS_PROJECTs.Where(j => j.Project_ID == intProject_ID).SingleOrDefault();
            }

            o.Contract_ID = Convert.ToInt32(Contract_ID);
            o.Code = Code;
            if (!string.IsNullOrEmpty(Name)) o.Name = Name;
            if (!string.IsNullOrEmpty(Kickoff_Date)) o.Kickoff_Date = Convert.ToDateTime(Kickoff_Date);
            if (!string.IsNullOrEmpty(End_Date)) o.End_Date = Convert.ToDateTime(End_Date);
            if (!string.IsNullOrEmpty(Resource)) o.Resource = Convert.ToInt32(Resource);
            if (!string.IsNullOrEmpty(Project_Leader)) o.Project_Leader = Convert.ToInt32(Project_Leader);
            if (!string.IsNullOrEmpty(CAT_Project_Step_ID))
                o.CAT_Project_Step_ID = Convert.ToInt32(CAT_Project_Step_ID);
            if (!string.IsNullOrEmpty(CAT_Project_Plan_Status_ID))
                o.CAT_Project_Plan_Status_ID = Convert.ToInt32(CAT_Project_Plan_Status_ID);
            if (!string.IsNullOrEmpty(CreatedBy)) o.CreatedBy = Convert.ToInt32(CreatedBy);
            if (!string.IsNullOrEmpty(CreatedOn)) o.CreatedOn = Convert.ToDateTime(CreatedOn);
            if (!string.IsNullOrEmpty(Project_Director)) o.Project_Director = Convert.ToInt32(Project_Director);
            o.Active = Active;

            if (isInsert)
            {
                db.ud_gSDMS_PROJECTs.InsertOnSubmit(o);
            }
            db.SubmitChanges();
            Project_ID = o.Project_ID.ToString();
        }


        static public DataTable ud_gSDMS_PROJECT_STEP_LstnotFinsih()
        {
            var db = new gSDMS_DataLinq();
            var temp = (from c in db.ud_gSDMS_CAT_PROJECT_STEPs
                        where c.CAT_Project_Step_ID != 10
                        select new
                        {
                            c.CAT_Project_Step_ID,
                            c.Name
                        }).Distinct();

            return Library.LINQToDataTable(db, temp);
        }

        static public DataTable ud_gSDMS_PROJECT_STEP_LstFinsih()
        {
            var db = new gSDMS_DataLinq();
            var temp = (from c in db.ud_gSDMS_CAT_PROJECT_STEPs
                        where c.CAT_Project_Step_ID == 10
                        select new
                        {
                            c.CAT_Project_Step_ID,
                            c.Name
                        }).Distinct();

            return Library.LINQToDataTable(db, temp);
        }

        #region  "Tim kiem du lieu tu bang project List moi ket cac ban lai voi nhau"
        public static DataTable ud_gSDMS_PROJECT_Search_New(string fromDate, string toDate, string status)
        {
            var mindate = Convert.ToDateTime(System.Configuration.ConfigurationManager.AppSettings["MinDay"]);
            var p_fromdate = string.IsNullOrEmpty(fromDate) ? mindate : Convert.ToDateTime(fromDate + " 0:0:0");
            var p_todate = string.IsNullOrEmpty(toDate) ? DateTime.Now : Convert.ToDateTime(toDate + " 23:59:59");

            var db = new gSDMS_DataLinq();
            object data;
            //data = from u in db.mp_Users
            //            join p in db.ud_gSDMS_PROJECT_ROLEs on u.UserID equals p.UserID
            //            join r in db.ud_gSDMS_CAT_USER_ROLEs on p.Cat_User_Role_ID equals r.CAT_User_Role_ID
            //       join t in db.ud_gSDMS_PROJECTs on p.Project_ID equals t.Project_ID


            data = (from t in db.ud_gSDMS_PROJECTs
                    join p in db.ud_gSDMS_PROJECT_ROLEs on t.Project_ID equals p.Project_ID
                    join u in db.mp_Users on p.UserID equals u.UserID
                    where
                       (p_fromdate <= t.CreatedOn && t.CreatedOn <= p_todate)
                        && (string.IsNullOrEmpty(status) ? true : t.CAT_Project_Plan_Status_ID == Convert.ToInt32(status))
                    select new
                    {
                        t.Project_ID,
                        t.Code,
                        t.Name,
                        CAT_Project_Step_ID = t.ud_gSDMS_CAT_PROJECT_STEP.Name,
                        Contract_ID = t.ud_gSDMS_CONTRACT.Contract_Code,
                        t.Kickoff_Date,
                        t.End_Date,
                        t.Project_Leader,
                        Leader = u.LoginName,
                        //Project_Leader = mp_User_Search(null,null,null,null,null,null,"Engineering").Rows[i].,
                        CAT_Project_Plan_Status_ID = t.ud_gSDMS_CAT_PROJECT_PLAN_STATUS.Name
                    }).Distinct();

            return Library.LINQToDataTable(db, data);
        }
        #endregion

        #region "lay du Project_ID tu bang cac bang Project"
        public static ud_gSDMS_PROJECT ud_gSDMS_PROJECT_Get(string id)
        {
            var intId = string.IsNullOrEmpty(id) ? -1 : Convert.ToInt32(id);
            if (intId == -1) return null;
            var db = new gSDMS_DataLinq();
            var query = db.ud_gSDMS_PROJECTs.Where(j => j.Project_ID == intId).SingleOrDefault();
            return query;
        }
        #endregion  

        #region Lay thong tin work step...
        public static DataTable GetWork_Step(string id)
        {
            var intId = string.IsNullOrEmpty(id) ? -1 : Convert.ToInt32(id);
            if (intId == -1) return null;
            var db = new gSDMS_DataLinq();
            var query = from j in db.ud_gSDMS_CAT_PROJECT_STEPs
                        join k in db.ud_gSDMS_PROJECTs on j.CAT_Project_Step_ID equals k.CAT_Project_Step_ID

                        where k.Project_ID == intId
                        select j;
            return Library.LINQToDataTable(db, query);
        }
        #endregion

        #region "Lay thong tin User dua vao Project_ID"
        public static DataTable getMemberList(string project_id)
        {
            var db = new gSDMS_DataLinq();
            var query = (from u in db.mp_Users
                         join p in db.ud_gSDMS_PROJECT_ROLEs on u.UserID equals p.UserID
                         join r in db.mp_Roles on p.RoleID equals r.RoleID
                         where p.Project_ID == Convert.ToInt32(project_id)
                         select new
                         {
                             p.Project_ID,
                             u.UserID,
                             u.LoginName,
                             u.Name,
                             u.Occupation,
                             name2 = r.DisplayName,
                             u.Email
                         }).Distinct();
            return Library.LINQToDataTable(db, query);
        }
        #endregion

        #region  "Tim kiem du lieu tu bang project List "
        public static DataTable ud_gSDMS_PROJECT_Search(string fromDate, string toDate, string projectID, string status)
        {
            var mindate = Convert.ToDateTime(System.Configuration.ConfigurationManager.AppSettings["MinDay"]);
            var p_fromdate = string.IsNullOrEmpty(fromDate) ? mindate : Convert.ToDateTime(fromDate + " 0:0:0");
            var p_todate = string.IsNullOrEmpty(toDate) ? DateTime.Now : Convert.ToDateTime(toDate + " 23:59:59");

            var db = new gSDMS_DataLinq();
           
            //data = from u in db.mp_Users
            //            join p in db.ud_gSDMS_PROJECT_ROLEs on u.UserID equals p.UserID
            //            join r in db.ud_gSDMS_CAT_USER_ROLEs on p.Cat_User_Role_ID equals r.CAT_User_Role_ID
            //       join t in db.ud_gSDMS_PROJECTs on p.Project_ID equals t.Project_ID


            var data = (from t in db.ud_gSDMS_PROJECTs
                        //join p in db.ud_gSDMS_PROJECT_ROLEs on t.Project_ID equals p.Project_ID
                        //join u in db.mp_Users on p.UserID equals u.UserID
                        where
                           (p_fromdate <= t.CreatedOn && t.CreatedOn <= p_todate)
                            && (string.IsNullOrEmpty(projectID) ? true : t.Project_ID.ToString().Equals(projectID))
                            && (string.IsNullOrEmpty(status) ? true : t.CAT_Project_Plan_Status_ID == Convert.ToInt32(status))
                        orderby t.Kickoff_Date.Value descending
                        select new
                        {
                            t.Project_ID,
                            t.Code,
                            t.Name,
                            CAT_Project_Step_ID = t.ud_gSDMS_CAT_PROJECT_STEP.Name,
                            Contract_ID = t.ud_gSDMS_CONTRACT.Contract_Code,
                            t.Kickoff_Date,
                            t.End_Date,
                            Project_Leader = (from c in db.mp_Users
                                              where t.Project_Leader == c.UserID
                                              select c.LoginName).FirstOrDefault(),
                            //Leader = u.LoginName,
                            //Project_Leader = mp_User_Search(null,null,null,null,null,null,"Engineering").Rows[i].,
                            CAT_Project_Plan_Status_ID = t.ud_gSDMS_CAT_PROJECT_PLAN_STATUS.Name
                        });

            return Library.LINQToDataTable(db, data);
        }
        #endregion

        #region Lay member tham gia vao du an
        static public bool mp_User_GetIsRole(int userID, int role)
        {
            var db = new gSDMS_DataLinq();
            var temp = (
                        from user in db.mp_Users
                        from j in db.ud_gSDMS_PROJECT_ROLEs
                        where
                        (
                            (j.RoleID == role) &&
                            (user.UserID == j.UserID) &&
                            (j.UserID == userID)
                        )
                        select user).ToList();
            return temp.Count > 0 ? true : false;
        }
        #endregion

        #region Lay kiem tra quyen project_Leader hoac project_director khi tham gia vao du an
        static public bool mp_User_GetIsRole(string project_id, string currentuserid)
        {
            var db = new gSDMS_DataLinq();
            int user_id = Convert.ToInt32(currentuserid);
            var temp = (
                        from j in db.ud_gSDMS_PROJECTs
                        where
                        (
                            j.Project_Leader == j.mp_User.UserID
                            && j.Project_Leader == user_id
                        )
                        select j).ToList();
            return temp.Count > 0 ? true : false;
        }
        #endregion

        #region "Ham lay cac thanh vien tham gia du an voi vai tro la project_leader"
        public static DataTable GetMemberProjectLeader()
        {
            var db = new gSDMS_DataLinq();
            var query = (db.ud_gSDMS_PROJECTs.Where(i => i.Project_Leader == i.mp_User.UserID).Select(i => new
            {
                i.mp_User.UserID,
                i.mp_User.LoginName,
                i.mp_User.Name
            })).Distinct();
            return Library.LINQToDataTable(db, query);
        }
        #endregion

        #region "Ham thong ke theo giai doan trong project <Report_Weekly>......"


        public static DataTable ud_gSDMS_Project_Summary(string fromDate, string toDate, string status, string leader, string projectcode, string projectname)
        {
            var mindate = Convert.ToDateTime(System.Configuration.ConfigurationManager.AppSettings["MinDay"]);
            var p_fromdate = string.IsNullOrEmpty(fromDate) ? mindate : Convert.ToDateTime(fromDate + " 0:0:0");
            var p_todate = string.IsNullOrEmpty(toDate) ? DateTime.Now : Convert.ToDateTime(toDate + " 0:0:0");

            var db = new gSDMS_DataLinq();
            var tb = (from j in db.ud_gSDMS_PROJECT_PLANs
                      where (p_fromdate <= j.CreatedOn && j.CreatedOn <= p_todate)
                            && (string.IsNullOrEmpty(status) ? true : j.CAT_Project_Step_ID == Convert.ToInt32(status))
                            && (string.IsNullOrEmpty(leader) ? true : j.ud_gSDMS_PROJECT.Project_Leader == Convert.ToInt32(leader))
                            && (string.IsNullOrEmpty(projectcode) ? true : j.Project_ID == Convert.ToInt32(projectcode))
                            && (string.IsNullOrEmpty(projectname) ? true : j.Project_ID == Convert.ToInt32(projectname))
                      orderby j.From_Date descending
                      select new
                      {
                          Project_ID = j.Project_Plan_ID,
                          projectcode = j.ud_gSDMS_PROJECT.Code,
                          projectname = j.ud_gSDMS_PROJECT.Name,
                          projectleader = (from k in db.mp_Users
                                           where j.ud_gSDMS_PROJECT.Project_Leader == k.UserID
                                           select k.Name).FirstOrDefault(),
                          work = j.ud_gSDMS_CAT_PROJECT_STEP.Name,
                          fromdate = j.From_Date,
                          todate = j.To_Date,
                          //late = (j.ud_gSDMS_PROJECT.Actual_End_Date == null ? true :
                          //((from l in db.ud_gSDMS_TIMESHEETs
                          //  where j.Project_ID == l.Project_ID && l.CAT_TimeSheet_Status_ID == 2 && j.CAT_Project_Step_ID == l.CAT_Project_Step_ID
                          //  select l.Hours).Sum() > (from a in db.ud_gSDMS_PROJECT_PLANs
                          //                           where a.Project_ID == j.ud_gSDMS_PROJECT.Project_ID && a.CAT_Project_Step_ID == j.ud_gSDMS_CAT_PROJECT_STEP.CAT_Project_Step_ID
                          //                           select a.Hours).Sum())) ? "x" : null,
                          late = ((from l in db.ud_gSDMS_TIMESHEETs
                                   where j.Project_ID == l.Project_ID && l.CAT_TimeSheet_Status_ID == 2 && j.CAT_Project_Step_ID == l.CAT_Project_Step_ID
                                   select l.Date).FirstOrDefault() > (from a in db.ud_gSDMS_PROJECT_PLANs
                                                                      where a.Project_ID == j.ud_gSDMS_PROJECT.Project_ID && a.CAT_Project_Step_ID == j.ud_gSDMS_CAT_PROJECT_STEP.CAT_Project_Step_ID
                                                                      select a.To_Date).FirstOrDefault()) ? "x" : null,
                          mandayestimate = (from a in db.ud_gSDMS_PROJECT_PLANs
                                            where a.Project_ID == j.ud_gSDMS_PROJECT.Project_ID && a.CAT_Project_Step_ID == j.ud_gSDMS_CAT_PROJECT_STEP.CAT_Project_Step_ID
                                            select a.Hours).Sum() / 8 > 0 ? (from a in db.ud_gSDMS_PROJECT_PLANs
                                                                             where a.Project_ID == j.ud_gSDMS_PROJECT.Project_ID && a.CAT_Project_Step_ID == j.ud_gSDMS_CAT_PROJECT_STEP.CAT_Project_Step_ID
                                                                             select a.Hours).Sum() / 8 : 0,
                          mandayused = (from l in db.ud_gSDMS_TIMESHEETs
                                        where j.Project_ID == l.Project_ID && l.CAT_TimeSheet_Status_ID == 2 && j.CAT_Project_Step_ID == l.CAT_Project_Step_ID
                                        select l.Hours).Sum() / 8 > 0 ? (from l in db.ud_gSDMS_TIMESHEETs
                                                                         where j.Project_ID == l.Project_ID && l.CAT_TimeSheet_Status_ID == 2 && j.CAT_Project_Step_ID == l.CAT_Project_Step_ID
                                                                         select l.Hours).Sum() / 8 : 0,
                          mandayleft = ((from a in db.ud_gSDMS_PROJECT_PLANs
                                         where a.Project_ID == j.ud_gSDMS_PROJECT.Project_ID && a.CAT_Project_Step_ID == j.ud_gSDMS_CAT_PROJECT_STEP.CAT_Project_Step_ID
                                         select a.Hours).Sum() / 8) - ((from l in db.ud_gSDMS_TIMESHEETs
                                                                        where j.Project_ID == l.Project_ID && l.CAT_TimeSheet_Status_ID == 2 && j.CAT_Project_Step_ID == l.CAT_Project_Step_ID
                                                                        select l.Hours).Sum() / 8 > 0 ? (from l in db.ud_gSDMS_TIMESHEETs
                                                                                                         where j.Project_ID == l.Project_ID && l.CAT_TimeSheet_Status_ID == 2 && j.CAT_Project_Step_ID == l.CAT_Project_Step_ID
                                                                                                         select l.Hours).Sum() / 8 : 0)
                      }).Distinct();
            var tb1 = (from j in tb
                       select new
                       {
                           j.projectcode,
                           j.projectleader,
                           j.projectname,
                           j.work,
                           j.late,
                           j.mandayused,
                           j.mandayleft,
                           j.mandayestimate,
                       }).Distinct();

            DataTable data = new DataTable("ListAssign");

            data.Columns.Add("projectcode");
            data.Columns.Add("projectname");
            data.Columns.Add("projectleader");

            data.Columns.Add("work");
            data.Columns.Add("fromdate");
            data.Columns.Add("todate");
            data.Columns.Add("late");
            data.Columns.Add("mandayestimate");
            data.Columns.Add("mandayused");
            data.Columns.Add("mandayleft");

            foreach (var p in tb1)
            {
                var plans = from c in tb
                            where c.projectcode.Equals(p.projectcode)
                            && c.work.Equals(p.work)
                            select c;
                var row = data.NewRow();

                row["projectcode"] = p.projectcode;
                row["projectleader"] = p.projectleader;
                row["projectname"] = p.projectname;
                row["work"] = p.work;
                row["mandayestimate"] = p.mandayestimate.Value;
                row["mandayleft"] = p.mandayleft.Value;
                row["mandayused"] = p.mandayused.Value;
                row["fromdate"] = (from c in plans
                                   select c.fromdate).Min();
                row["todate"] = (from c in plans
                                 select c.todate).Max();
                row["late"] = (from c in plans
                               where c.late.Equals("x")
                               select c.late).Count() > 0 ? "x" : null;
                data.Rows.Add(row);
            }
            return data;
        }
        #endregion

        #region "Ham thong ke tong ket trong project <Report_Total>.........."
        public static DataTable ud_gSDMS_Project_ReportTotal(string fromDate, string toDate, string leader, string projectname)
        {
            var mindate = Convert.ToDateTime(System.Configuration.ConfigurationManager.AppSettings["MinDay"]);
            var p_fromdate = string.IsNullOrEmpty(fromDate) ? mindate : Convert.ToDateTime(fromDate + " 0:0:0");
            var p_todate = string.IsNullOrEmpty(toDate) ? DateTime.Now : Convert.ToDateTime(toDate + " 0:0:0");
            //double Time_late;
            var db = new gSDMS_DataLinq();
            var tb = from j in db.ud_gSDMS_PROJECTs
                     where (p_fromdate <= j.CreatedOn && j.CreatedOn <= p_todate)
                          && (string.IsNullOrEmpty(leader) ? true : j.Project_Leader == Convert.ToInt32(leader))
                          && (string.IsNullOrEmpty(projectname) ? true : j.Project_ID == Convert.ToInt32(projectname))
                     select new
                     {
                         j.Project_ID,
                         Project_code = j.Code,
                         Project_name = j.Name,
                         Project_leader = (from k in db.mp_Users
                                           where j.Project_Leader == k.UserID
                                           select k.Name).FirstOrDefault(),
                         work = j.ud_gSDMS_CAT_PROJECT_STEP.Name,
                         Start_date = j.Kickoff_Date,
                         //Ngày nghiệm thu thực tế
                         End_date = j.End_Date,
                         //Ngày nghiệm thu thực tế - Ngày nghiệm thu kế hoạch
                         Time_late = j.Actual_End_Date == null ? 0 : (j.Actual_End_Date.Value.Date - j.End_Date.Value.Date).TotalDays,
                         Resource_actual = (from k in db.ud_gSDMS_TIMESHEETs
                                            where j.Project_ID == k.Project_ID && k.CAT_TimeSheet_Status_ID == 2
                                            select k.Hours).Sum() / 8 > 0 ? (from k in db.ud_gSDMS_TIMESHEETs
                                                                             where j.Project_ID == k.Project_ID && k.CAT_TimeSheet_Status_ID == 2
                                                                             select k.Hours).Sum() / 8 : 0,
                         Resource_estimate = j.Resource,
                         Resource_over = ((from k in db.ud_gSDMS_TIMESHEETs
                                           where j.Project_ID == k.Project_ID && k.CAT_TimeSheet_Status_ID == 2
                                           select k.Hours).Sum() / 8 > 0 ? (from k in db.ud_gSDMS_TIMESHEETs
                                                                            where j.Project_ID == k.Project_ID && k.CAT_TimeSheet_Status_ID == 2
                                                                            select k.Hours).Sum() / 8 : 0) - (j.Resource > 0 ? j.Resource : 0)//Ngày nghiệm thu thực tế - Ngày nghiệm thu kế hoạch
                     };
            return Library.LINQToDataTable(db, tb);
        }
        // "Ham tru ngay trong C# de dua vao cau truy van Linq"
        private static Double GetNgay(DateTime fromdate, DateTime toDate)
        {
            Double songay = (toDate.Date - fromdate.Date).TotalDays;
            return songay;
        }
        #endregion

        public static List<ud_gSDMS_PROJECT> ud_gSDMS_PROJECT_Lst_From_User(int userID)
        {
            var db = new gSDMS_DataLinq();
            var query = (from u in db.mp_Users
                         join p in db.ud_gSDMS_PROJECT_ROLEs on u.UserID equals p.UserID
                         where p.UserID == Convert.ToInt32(userID)
                         select
                             p.ud_gSDMS_PROJECT
                         ).Distinct();
            return query.ToList();
        }

        public static bool mp_User_Is_In_Project(int userID, int projectID)
        {
            var db = new gSDMS_DataLinq();
            var query = (from p in db.ud_gSDMS_PROJECT_ROLEs

                         where p.UserID == Convert.ToInt32(userID)
                         && p.Project_ID == projectID
                         select
                             p.ud_gSDMS_PROJECT
                         ).Distinct();
            return query.Count() > 0;
        }

        public static bool mp_User_Is_In_Any_Project(int userID)
        {
            var db = new gSDMS_DataLinq();
            var query = (from p in db.ud_gSDMS_PROJECT_ROLEs

                         where p.UserID == Convert.ToInt32(userID)

                         select
                             p.ud_gSDMS_PROJECT
                         ).Distinct();
            return query.Count() > 0;
        }

        #region update lai trang thai CAT_Project_Step_ID = pm chon trong Chang status
        public static void ud_gSDMS_PROJECT_UpdChangStatus(ref string Project_ID, string CAT_Project_Step_ID, string FinishDate)
        {
            bool isInsert = string.IsNullOrEmpty(Project_ID);
            var db = new gSDMS_DataLinq();
            ud_gSDMS_PROJECT o;
            if (isInsert)
            {
                o = new ud_gSDMS_PROJECT();
            }
            else
            {
                var intProject_ID = Convert.ToInt32(Project_ID);
                o = db.ud_gSDMS_PROJECTs.Where(j => j.Project_ID == intProject_ID).SingleOrDefault();
            }
            if (!string.IsNullOrEmpty(CAT_Project_Step_ID))
                o.CAT_Project_Step_ID = Convert.ToInt32(CAT_Project_Step_ID);
            if (!string.IsNullOrEmpty(FinishDate))
                o.Actual_End_Date = Convert.ToDateTime(FinishDate);
            if (isInsert)
            {
                db.ud_gSDMS_PROJECTs.InsertOnSubmit(o);
            }
            db.SubmitChanges();
            Project_ID = o.Project_ID.ToString();
        }
        #endregion

        static public List<mp_User> mp_User_IsRoleProject(int userID, string role)
        {
            var db = new gSDMS_DataLinq();
            var temp = (
                        from user in db.mp_Users
                        from userRole in db.mp_UserRoles
                        from Role in db.mp_Roles
                        where
                        (
                            (user.UserID == userRole.UserID) &&
                            (userRole.RoleID == Role.RoleID) &&
                            (Role.DisplayName == role) &&
                            (user.UserID == userID)
                        )
                        select user).ToList();
            return temp;
        }

        #region "update lai Cat_Project_Plan_Status_ID trong Project......"
        public static void ud_gSDMS_PROJECT_updStatus(string Project_ID)
        {
            var db = new gSDMS_DataLinq();
            ud_gSDMS_PROJECT o;
            int intProject_ID = Convert.ToInt32(Project_ID);
            o = db.ud_gSDMS_PROJECTs.Where(j => j.Project_ID == intProject_ID).SingleOrDefault();
            o.CAT_Project_Plan_Status_ID = 2;
            db.SubmitChanges();
        }
        #endregion

        #region " lay Account nhung nguoi lien quan trong du an"
        public static DataTable getAccountID()//string currentuser)
        {
            //int user = Convert.ToInt32(currentuser);
            var db = new gSDMS_DataLinq();
            var query = (from j in db.ud_gSDMS_PROJECTs
                         join k in db.ud_gSDMS_TIMESHEETs on j.Project_ID equals k.Project_ID
                         join m in db.mp_Users on k.Account equals m.UserID
                         // where k.Account == user
                         select new
                         {
                             m.UserID,
                             m.LoginName,
                             m.Name
                         }).Distinct();

            return Library.LINQToDataTable(db, query);
        }

        public static DataTable getListAccountID(string projectid)//string currentuser)
        {
            var db = new gSDMS_DataLinq();
            object query;
            if (string.IsNullOrEmpty(projectid))
            {
                query = null;
            }
            else
            {
                query = (from j in db.ud_gSDMS_TIMESHEETs
                         join k in db.ud_gSDMS_PROJECT_ROLEs on j.Project_ID equals k.Project_ID
                         join m in db.mp_Users on j.Account equals m.UserID
                         where (j.Project_ID == Convert.ToInt32(projectid))
                         select new
                         {
                             m.UserID,
                             m.LoginName,
                             m.Name
                         }).Distinct();
            }

            return Library.LINQToDataTable(db, query);
        }
        #endregion

        #region " lay Account nhung nguoi lien quan trong du an"
        public static DataTable getAccountWhileComboID(string project_id)//string currentuser)
        {
            var db = new gSDMS_DataLinq();
            int id;
            if (string.IsNullOrEmpty(project_id)) return null;
            else
            {
                id = Convert.ToInt32(project_id);
                var query = (from j in db.ud_gSDMS_PROJECTs
                             join k in db.ud_gSDMS_TIMESHEETs on j.Project_ID equals k.Project_ID
                             join m in db.mp_Users on k.Account equals m.UserID
                             where j.Project_ID == id
                             select new
                             {
                                 m.UserID,
                                 m.LoginName,
                                 m.Name
                             }).Distinct();
                return Library.LINQToDataTable(db, query);
            }
        }
        #endregion

        #region update Project feedback_Comment
        public static void ud_gSDMS_PROJECT_PLAN_updFeedBack(string Project_Plan_ID, string FeedBack)
        {
            var db = new gSDMS_DataLinq();
            ud_gSDMS_PROJECT_PLAN o;
            var intid = Convert.ToInt32(Project_Plan_ID);
            o = db.ud_gSDMS_PROJECT_PLANs.Where(i => i.Project_Plan_ID == intid).SingleOrDefault();
            o.FeedBack_Comment = FeedBack;
            db.SubmitChanges();
            //Project_ID = o.Project_ID.ToString();
        }
        #endregion

        #region "Lay ComboProjectCode theo nhung nguoi lien quan trong du an"
        public static DataTable GetRoleID(int userid)
        {
            var db = new gSDMS_DataLinq();
            var result = (from p in db.ud_gSDMS_PROJECT_ROLEs
                          from u in db.ud_gSDMS_PROJECTs
                          where (p.UserID.Equals(userid))
                          select p).Distinct();
            return Library.LINQToDataTable(db, result);
        }

        public static bool GetRoleIDSheet(int userid)
        {
            var db = new gSDMS_DataLinq();
            var result = (from u in db.ud_gSDMS_PROJECTs
                          where u.Project_Director == userid
                          select u);
            if (result.Count() > 0) return true;
            else return false;
        }

        public static DataTable getComboProjectCode(int userid)
        {
            var db = new gSDMS_DataLinq();
            object query;
            string roleid = GetRoleID(userid).Columns[0].ColumnName.ToString();
            switch (roleid)
            {
                case "12":
                    {
                        query = (from j in db.ud_gSDMS_PROJECTs
                                 where (j.Project_Director==userid)
                                 && j.CAT_Project_Step_ID.Equals(10) == false
                                 select new
                                            {
                                                j.Project_ID,
                                                j.Code
                                            }).Distinct();
                    }
                    break;
                case "11":
                    {
                        query = (from j in db.ud_gSDMS_PROJECTs
                                 where (j.Project_Leader == userid
                                 && j.CAT_Project_Step_ID.Equals(10) == false)
                                 select new
                                 {
                                     j.Project_ID,
                                     j.Code
                                 }).Distinct();
                    }
                    break;
                case "1":
                    {
                        query = (from j in db.ud_gSDMS_PROJECTs
                                 where (j.CAT_Project_Step_ID.Equals(10) == false)
                                 select new
                                 {
                                     j.Project_ID,
                                     j.Code
                                 }).Distinct();
                    }
                    break;
                default:
                    {
                        query = (from j in db.ud_gSDMS_PROJECTs
                                 from k in db.ud_gSDMS_PROJECT_ROLEs
                                 where ((j.Project_ID == k.Project_ID) && (k.UserID == userid)
                                 &&  j.CAT_Project_Step_ID.Equals(10) == false)
                                 select new
                                            {
                                                j.Project_ID,
                                                j.Code
                                            }).Distinct();
                    }
                    break;
            }
            if (userid ==1 || userid ==16)
            {
                query = (from j in db.ud_gSDMS_PROJECTs
                         where j.CAT_Project_Step_ID.Equals(10) == false
                         select new
                         {
                             j.Project_ID,
                             j.Code
                         }).Distinct();
            }
            return Library.LINQToDataTable(db, query);
        }
        #endregion

        public static DataTable ud_gSDMS_PROJECT_Leader_Tab()
        {
            var db = new gSDMS_DataLinq();

            var data = (from c in db.ud_gSDMS_PROJECTs
                        select c.mp_User).Distinct();

            return Library.LINQToDataTable(db, data);
        }
        #endregion

        #region ud_gSDMS_PROJECT_ROLE

        public static void ud_gSDMS_PROJECT_ROLE_Upd(ref string Project_Role_ID, string Project_ID, string UserID,
                                                     string roleID, bool Active)
        {
            bool isInsert = string.IsNullOrEmpty(Project_Role_ID);

            var db = new gSDMS_DataLinq();
            ud_gSDMS_PROJECT_ROLE o;

            if (isInsert)
            {
                o = new ud_gSDMS_PROJECT_ROLE();
            }
            else
            {
                var intProject_Role_ID = Convert.ToInt32(Project_Role_ID);
                o = db.ud_gSDMS_PROJECT_ROLEs.Where(j => j.Project_Role_ID == intProject_Role_ID).SingleOrDefault();
            }

            if (!string.IsNullOrEmpty(Project_ID)) o.Project_ID = Convert.ToInt32(Project_ID);
            if (!string.IsNullOrEmpty(UserID)) o.UserID = Convert.ToInt32(UserID);
            if (!string.IsNullOrEmpty(roleID)) o.RoleID = Convert.ToInt32(roleID);
            o.Active = Active;

            if (isInsert)
            {
                db.ud_gSDMS_PROJECT_ROLEs.InsertOnSubmit(o);
            }
            db.SubmitChanges();
            Project_Role_ID = o.Project_ID.ToString();
        }

        public static DataTable ud_gSDMS_PROJECT_ROLE_Search(string Project_Role_ID, string Project_ID, string UserID,
                                                             string Cat_User_Role_ID, bool Active)
        {
            var db = new gSDMS_DataLinq();
            var data = from j in db.ud_gSDMS_PROJECT_ROLEs
                       where
                           (string.IsNullOrEmpty(Project_Role_ID)
                                ? true
                                : j.Project_Role_ID == Convert.ToInt32(Project_Role_ID))
                           && (string.IsNullOrEmpty(Project_ID) ? true : j.Project_ID == Convert.ToInt32(Project_ID))
                           && (string.IsNullOrEmpty(Project_ID) ? true : j.Project_ID == Convert.ToInt32(Project_ID))
                           && (string.IsNullOrEmpty(UserID) ? true : j.UserID == Convert.ToInt32(UserID))
                           &&
                           (string.IsNullOrEmpty(Cat_User_Role_ID)
                                ? true
                                : j.RoleID == Convert.ToInt32(Cat_User_Role_ID))
                           && j.Active == Active
                       select new
                                  {
                                      j.Project_Role_ID,
                                      j.Project_ID,
                                      j.UserID,
                                      j.RoleID,
                                      j.Active,
                                      j.mp_User.LoginName,
                                      RoleName = j.mp_Role.DisplayName
                                  };

            return Library.LINQToDataTable(db, data);
        }

        public static void ud_gSDMS_PROJECT_ROLE_Del(string Project_Role_ID, string Project_ID)
        {
            var db = new gSDMS_DataLinq();
            var o = from j in db.ud_gSDMS_PROJECT_ROLEs
                    where
                        (string.IsNullOrEmpty(Project_Role_ID)
                             ? true
                             : j.Project_Role_ID == Convert.ToInt32(Project_Role_ID))
                        && (string.IsNullOrEmpty(Project_ID) ? true : j.Project_ID == Convert.ToInt32(Project_ID))
                    select j;

            if (o.Count() > 0)
            {
                db.ud_gSDMS_PROJECT_ROLEs.DeleteAllOnSubmit(o);
                db.SubmitChanges();
            }
        }

        #endregion

        #region ud_gSDMS_TIMESHEET

        #region "lay du TimeSheet_ID tu bang cac bang timesheet"
        public static ud_gSDMS_TIMESHEET ud_gSDMS_TIMESHEET_Get(string id)
        {
            var intId = string.IsNullOrEmpty(id) ? -1 : Convert.ToInt32(id);
            if (intId == -1) return null;
            var db = new gSDMS_DataLinq();
            var query = db.ud_gSDMS_TIMESHEETs.Where(j => j.TimeSheet_ID == intId).SingleOrDefault();
            return query;
        }
        #endregion  

        #region  "Tim kiem du lieu tu bang TimeSheet danh cho quan tri du an"
        public static DataTable ud_gSDMS_TIMESHEET_Search_Admin(string fromDate, string toDate, string account, string status, string project)
        {
            var mindate = Convert.ToDateTime(System.Configuration.ConfigurationManager.AppSettings["MinDay"]);
            var p_fromdate = string.IsNullOrEmpty(fromDate) ? mindate : Convert.ToDateTime(fromDate + " 0:0:0");
            var p_todate = string.IsNullOrEmpty(toDate) ? DateTime.Now : Convert.ToDateTime(toDate + " 23:59:59");

            var db = new gSDMS_DataLinq();
            object data;

            data = from t in db.ud_gSDMS_TIMESHEETs join j in db.mp_Users on t.ApprovedBy equals j.UserID 
            
                    where
                       (p_fromdate <= t.Date && t.Date <= p_todate)
                        && (string.IsNullOrEmpty(account) ? true :t.mp_User.UserID == Convert.ToInt32(account))
                        && (string.IsNullOrEmpty(status) ? true : t.CAT_TimeSheet_Status_ID == Convert.ToInt32(status))
                        && (string.IsNullOrEmpty(project) ? true :t.Project_ID==Convert.ToInt32(project))
                        orderby t.Date ascending 
                        select new
                              {
                                  t.TimeSheet_ID,
                                  t.Date,
                                  Account = t.mp_User.LoginName,
                                  Project_Code = t.ud_gSDMS_PROJECT.Code,
                                  Work = t.ud_gSDMS_CAT_PROJECT_STEP.Name,
                                  Status = t.ud_gSDMS_CAT_TIMESHEET_STATUS.Name,
                                  t.From,
                                  t.To,
                                  t.Hours,
                                  CAT_Work_Status_ID = t.ud_gSDMS_CAT_WORK_STATUS.Name,
                                  t.Percent_Complete,
                                  t.FeedBack,
                                  t.Description,
                                  ApprovedBy = j.Name
                              };

            return Library.LINQToDataTable(db, data);
        }
        #endregion

        #region ud_gSDMS_TIMESHEET_User_Lst
        public static DataTable ud_gSDMS_TIMESHEET_User_Lst()
        {
            var db = new gSDMS_DataLinq();

            var data = (from c in db.ud_gSDMS_TIMESHEETs
                        select c.mp_User).Distinct();

            return Library.LINQToDataTable(db , data);
        }
        #endregion

        #region "Tim kiem theo tung account trong Timesheet................."
        public static DataTable ud_gSDMS_TIMESHEET_SearchApprove(string fromDate, string toDate, string project, string leader, string account, string status, string currentUser)
        {
            var mindate = Convert.ToDateTime(System.Configuration.ConfigurationManager.AppSettings["MinDay"]);
            var p_fromdate = string.IsNullOrEmpty(fromDate) ? mindate : Convert.ToDateTime(fromDate + " 0:0:0");
            var p_todate = string.IsNullOrEmpty(toDate) ? DateTime.Now : Convert.ToDateTime(toDate + " 23:59:59");
            var db = new gSDMS_DataLinq();
            int user = Convert.ToInt32(currentUser);
            object data;

            if (GetRoleIDSheet(user) == true || user==1)
            {
                data = from t in db.ud_gSDMS_TIMESHEETs
                       where (t.ud_gSDMS_PROJECT.Project_Director == user || user==1)
                             && (p_fromdate <= t.Date && t.Date <= p_todate)
                             && (string.IsNullOrEmpty(account) ? true : t.Account == Convert.ToInt32(account))
                             && (string.IsNullOrEmpty(status) ? true : t.CAT_TimeSheet_Status_ID == Convert.ToInt32(status))
                             && (string.IsNullOrEmpty(project) ? true : t.Project_ID == Convert.ToInt32(project))
                             && (string.IsNullOrEmpty(leader) ? true : t.ud_gSDMS_PROJECT.Project_Leader.ToString().Equals(leader))
                       orderby t.Date descending
                       select new
                                  {
                                      t.TimeSheet_ID,
                                      Project_Code = t.ud_gSDMS_PROJECT.Code,
                                      t.Date,
                                      t.mp_User.LoginName,
                                      Work = t.ud_gSDMS_CAT_PROJECT_STEP.Name,
                                      TimeSheet_Status = t.ud_gSDMS_CAT_TIMESHEET_STATUS.Name,
                                      t.From,
                                      t.To,
                                      t.Hours,
                                      Status = t.ud_gSDMS_CAT_WORK_STATUS.Name,
                                      t.Percent_Complete,
                                      t.FeedBack,
                                      t.Description,
                                      Leader = t.ud_gSDMS_PROJECT.mp_User.LoginName,
                                      ApprovedBy = t.mp_User.LoginName
                                  };
            }
            else
            {
                DataTable tb = GetRoleID(user);
                if(tb.Rows.Count<1) return null;
                else
                {
                  string roleid = tb.Rows[0]["RoleID"].ToString();
                  switch (roleid)
                  {
                      case "11":
                          {
                              data = from t in db.ud_gSDMS_TIMESHEETs
                                     where ((t.ud_gSDMS_PROJECT.Project_Leader == user)
                                            && (p_fromdate <= t.Date && t.Date <= p_todate)
                                            &&
                                            (string.IsNullOrEmpty(project)
                                                 ? true
                                                 : t.Project_ID == Convert.ToInt32(project))
                                            &&
                                            (string.IsNullOrEmpty(account)
                                                 ? true
                                                 : t.Account == Convert.ToInt32(account))
                                            &&
                                            (string.IsNullOrEmpty(status)
                                                 ? true
                                                 : t.CAT_TimeSheet_Status_ID == Convert.ToInt32(status)))
                                     orderby t.Date descending
                                     select new
                                                {
                                                    t.TimeSheet_ID,
                                                    Project_Code = t.ud_gSDMS_PROJECT.Code,
                                                    t.Date,
                                                    t.mp_User.LoginName,
                                                    Work = t.ud_gSDMS_CAT_PROJECT_STEP.Name,
                                                    TimeSheet_Status = t.ud_gSDMS_CAT_TIMESHEET_STATUS.Name,
                                                    t.From,
                                                    t.To,
                                                    t.Hours,
                                                    Status = t.ud_gSDMS_CAT_WORK_STATUS.Name,
                                                    t.Percent_Complete,
                                                    t.FeedBack,
                                                    t.Description,
                                                    Leader = t.ud_gSDMS_PROJECT.mp_User.LoginName,
                                                    ApprovedBy = t.mp_User.LoginName
                                                };
                          }
                          break;
                      default:
                          {
                              data = from t in db.ud_gSDMS_TIMESHEETs
                                     where (t.Account == user)
                                           && (p_fromdate <= t.Date && t.Date <= p_todate)
                                           &&
                                           (string.IsNullOrEmpty(account) ? true : t.Account == Convert.ToInt32(account))
                                           &&
                                           (string.IsNullOrEmpty(status)
                                                ? true
                                                : t.CAT_TimeSheet_Status_ID == Convert.ToInt32(status))
                                           &&
                                           (string.IsNullOrEmpty(project)
                                                ? true
                                                : t.Project_ID == Convert.ToInt32(project))
                                     orderby t.Date descending
                                     select new
                                                {
                                                    t.TimeSheet_ID,
                                                    Project_Code = t.ud_gSDMS_PROJECT.Code,
                                                    t.Date,
                                                    t.mp_User.LoginName,
                                                    Work = t.ud_gSDMS_CAT_PROJECT_STEP.Name,
                                                    TimeSheet_Status = t.ud_gSDMS_CAT_TIMESHEET_STATUS.Name,
                                                    t.From,
                                                    t.To,
                                                    t.Hours,
                                                    Status = t.ud_gSDMS_CAT_WORK_STATUS.Name,
                                                    t.Percent_Complete,
                                                    t.FeedBack,
                                                    t.Description,
                                                    ApprovedBy = t.mp_User.Name
                                                };
                          }
                          break;
                  }
                }
            }
            return Library.LINQToDataTable(db, data);
        }
        #endregion

        #region "Tim kiem list..TimeSheet.............................
        public static DataTable ud_gSDMS_TIMESHEET_Search(string fromDate, string toDate, string project, string status, string currentUser, string leader)
        {
            var mindate = Convert.ToDateTime(System.Configuration.ConfigurationManager.AppSettings["MinDay"]);
            var p_fromdate = string.IsNullOrEmpty(fromDate) ? mindate : Convert.ToDateTime(fromDate + " 0:0:0");
            var p_todate = string.IsNullOrEmpty(toDate) ? DateTime.Now : Convert.ToDateTime(toDate + " 23:59:59");
            var db = new gSDMS_DataLinq();
            int user = Convert.ToInt32(currentUser);
            object data;
            if (mp_User_IsRole(user,"1|12"))
            {
                data = from t in db.ud_gSDMS_TIMESHEETs
                       where (p_fromdate <= t.Date && t.Date <= p_todate)
                                       && (string.IsNullOrEmpty(status) ? true: t.CAT_TimeSheet_Status_ID == Convert.ToInt32(status))
                                       && (string.IsNullOrEmpty(project) ? true : t.Project_ID == Convert.ToInt32(project))
                                       && (string.IsNullOrEmpty(leader) ? true : t.ud_gSDMS_PROJECT.Project_Leader.ToString().Equals(leader))
                                 orderby t.Date descending
                                 select new
                                 {
                                     t.TimeSheet_ID,
                                     Project_Code = t.ud_gSDMS_PROJECT.Code,
                                     t.Date,
                                     t.mp_User.LoginName,
                                     Work = t.ud_gSDMS_CAT_PROJECT_STEP.Name,
                                     TimeSheet_Status = t.ud_gSDMS_CAT_TIMESHEET_STATUS.Name,
                                     t.From,
                                     t.To,
                                     t.Hours,
                                     Status = t.ud_gSDMS_CAT_WORK_STATUS.Name,
                                     t.Percent_Complete,
                                     t.FeedBack,
                                     t.Description,
                                     Leader = t.ud_gSDMS_PROJECT.mp_User.LoginName,
                                     ApprovedBy = t.mp_User.LoginName
                                 };
            }
            else
            {
                data = from t in db.ud_gSDMS_TIMESHEETs
                       where (t.Account == user)
                             && (p_fromdate <= t.Date && t.Date <= p_todate)
                             &&
                             (string.IsNullOrEmpty(status) ? true : t.CAT_TimeSheet_Status_ID == Convert.ToInt32(status))
                             && (string.IsNullOrEmpty(project) ? true : t.Project_ID == Convert.ToInt32(project))
                       orderby t.Date descending
                       select new
                                  {
                                      t.TimeSheet_ID,
                                      Project_Code = t.ud_gSDMS_PROJECT.Code,
                                      t.Date,
                                      t.mp_User.LoginName,
                                      Work = t.ud_gSDMS_CAT_PROJECT_STEP.Name,
                                      TimeSheet_Status = t.ud_gSDMS_CAT_TIMESHEET_STATUS.Name,
                                      t.From,
                                      t.To,
                                      t.Hours,
                                      Status = t.ud_gSDMS_CAT_WORK_STATUS.Name,
                                      t.Percent_Complete,
                                      t.FeedBack,
                                      t.Description,
                                      Leader = t.ud_gSDMS_PROJECT.mp_User.LoginName,
                                      ApprovedBy = t.mp_User.LoginName
                                  };
            }
            return Library.LINQToDataTable(db, data);
        }

        public static DataTable ud_gSDMS_TIMESHEET_SearchReport(string fromDate, string toDate, string account, string project, string status, string leader)
        {
            var mindate = Convert.ToDateTime(System.Configuration.ConfigurationManager.AppSettings["MinDay"]);
            var p_fromdate = string.IsNullOrEmpty(fromDate) ? mindate : Convert.ToDateTime(fromDate + " 0:0:0");
            var p_todate = string.IsNullOrEmpty(toDate) ? DateTime.Now : Convert.ToDateTime(toDate + " 23:59:59");
            var db = new gSDMS_DataLinq();
            object data = from t in db.ud_gSDMS_TIMESHEETs
                          where (p_fromdate <= t.Date && t.Date <= p_todate)
                                &&(string.IsNullOrEmpty(account) ? true: t.Account.ToString().Equals(account))
                                && (string.IsNullOrEmpty(status) ? true : t.CAT_TimeSheet_Status_ID.ToString().Equals(status))
                                && (string.IsNullOrEmpty(project) ? true : t.Project_ID.ToString().Equals(project))
                                && (string.IsNullOrEmpty(leader) ? true : t.ud_gSDMS_PROJECT.Project_Leader.ToString().Equals(leader))
                          orderby t.Date descending
                          select new
                          {
                              t.TimeSheet_ID,
                              Project_Code = t.ud_gSDMS_PROJECT.Code,
                              t.Date,
                              t.mp_User.LoginName,
                              Work = t.ud_gSDMS_CAT_PROJECT_STEP.Name,
                              TimeSheet_Status = t.ud_gSDMS_CAT_TIMESHEET_STATUS.Name,
                              t.From,
                              t.To,
                              t.Hours,
                              Status = t.ud_gSDMS_CAT_WORK_STATUS.Name,
                              t.Percent_Complete,
                              t.FeedBack,
                              t.Description,
                              Leader = t.ud_gSDMS_PROJECT.mp_User.LoginName,
                              ApprovedBy = (from c in db.mp_Users
                                            where t.ApprovedBy == c.UserID
                                            select c.LoginName).FirstOrDefault()
                          };
            return Library.LINQToDataTable(db, data);
        }

        public static DataTable ud_gSDMS_TIMESHEET_CheckList_Search(string fromDateString, string toDateString, string projectID, string leader)
        {
            var db = new gSDMS_DataLinq();

            long count = 1;
            var engineers = mp_UserRole_Search_RoleLst(0 , 5 , 0);

            DataTable data = new DataTable("Check_List");

            DataColumn col = new DataColumn("STT");
            data.Columns.Add(col);
            col = new DataColumn("Date");
            data.Columns.Add(col);
            col = new DataColumn("Project_ID");
            data.Columns.Add(col);
            col = new DataColumn("Project_Code");
            data.Columns.Add(col);
            col = new DataColumn("Leader");
            data.Columns.Add(col);

            foreach (var engineer in engineers)
            {
                col = new DataColumn(engineer.mp_User.LoginName);
                data.Columns.Add(col);
            }

            var dates = (from c in db.ud_gSDMS_TIMESHEETs
                         where (string.IsNullOrEmpty(fromDateString) ? true : Convert.ToDateTime(fromDateString).Date <= c.Date.Value.Date)
                         && (string.IsNullOrEmpty(toDateString) ? true : Convert.ToDateTime(toDateString).Date >= c.Date.Value.Date)
                         orderby c.Date descending
                        select c.Date.Value.Date).Distinct();

            foreach (var date in dates)
            {
                var timesheetsInDate = (from c in db.ud_gSDMS_TIMESHEETs
                                        where c.Date.Value.Date.Equals(date)
                                        select c);
                var projects = (from c in timesheetsInDate
                                where (string.IsNullOrEmpty(projectID) ? true : c.Project_ID.ToString().Equals(projectID))
                                && (string.IsNullOrEmpty(leader) ? true : c.ud_gSDMS_PROJECT.Project_Leader.ToString().Equals(leader))
                               select c.ud_gSDMS_PROJECT).Distinct();

                foreach (var project in projects)
                {
                    DataRow row = data.NewRow();

                    row["STT"] = count++;
                    row["Date"] = date.ToString("dd/MM/yyyy");
                    row["Project_Code"] = project.Code;
                    row["Project_ID"] = project.Project_ID;
                    row["Leader"] = project.mp_User.LoginName;

                    foreach (var engineer in engineers)
                    {
                        row[engineer.mp_User.LoginName] = (from c in timesheetsInDate
                                                           where (c.Account.Equals(engineer.UserID))
                                                           && (c.Project_ID.Equals(project.Project_ID))
                                                           select c.ud_gSDMS_CAT_TIMESHEET_STATUS.Name).FirstOrDefault();
                    }
                    data.Rows.Add(row);
                }

                
            }

            return data;
        }

        #endregion

        #region "Ham delete TimeSheet.........."
        public static void ud_gSDMS_TIMESHEET_Del(string id, bool isPermanent)
        {
            int intId = string.IsNullOrEmpty(id) ? -1 : Convert.ToInt32(id);
            var db = new gSDMS_DataLinq();
            var o = db.ud_gSDMS_TIMESHEETs.Where(j => j.TimeSheet_ID == intId).SingleOrDefault();
            if (o != null)
            {
                if (isPermanent)
                    db.ud_gSDMS_TIMESHEETs.DeleteOnSubmit(o);
                else
                {
                    //o.TimeSheet_ID = -1;
                }   
               db.SubmitChanges();
            }
            return;
        }
        #endregion

        #region " lay Account nhung nguoi lien quan trong du an"
        public static DataTable getAccountID(string currentuser)
        {
            int user = Convert.ToInt32(currentuser);
            var db = new gSDMS_DataLinq();
            var query = (from j in db.ud_gSDMS_PROJECTs
                         join k in db.ud_gSDMS_TIMESHEETs on j.Project_ID equals k.Project_ID
                         join m in db.mp_Users on k.Account equals m.UserID
                         where k.Account == user
                         select new
                         {
                             m.UserID,
                             m.LoginName,
                             m.Name
                         }).Distinct();

            return Library.LINQToDataTable(db, query);
        }
        #endregion

        #region "Ham save ud_gSDMS_TIMESHEET...................."

        public static void ud_gSDMS_TIMESHEET_Upd_Sua(ref string TimeSheet_ID, string Project_ID, string CAT_Project_Step_ID,
                                                  string Date, string From, string To, string Hours,
                                                  string CAT_TimeSheet_Status_ID, string CAT_Work_Status_ID,
                                                  string Percent_Complete, string Description, string FeedBack,
                                                  string ApprovedBy, string ApproveOn, string RejectedBy,
                                                  string RejectedOn)
        {
            bool isInsert = string.IsNullOrEmpty(TimeSheet_ID);
            var db = new gSDMS_DataLinq();
            ud_gSDMS_TIMESHEET o;
            if (isInsert)
            {
                o = new ud_gSDMS_TIMESHEET();
            }
            else
            {
                int intTimeSheet_ID = Convert.ToInt32(TimeSheet_ID);
                o = db.ud_gSDMS_TIMESHEETs.Where(j => j.TimeSheet_ID == intTimeSheet_ID).SingleOrDefault();
            }
            //o.TimeSheet_ID = Convert.ToInt32(TimeSheet_ID);
            o.Project_ID = Convert.ToInt32(Project_ID);
            o.CAT_Project_Step_ID = Convert.ToInt32(CAT_Project_Step_ID);
            if (!string.IsNullOrEmpty(Date)) o.Date = Convert.ToDateTime(Date);
            o.From = float.Parse(From);
            o.To = float.Parse(To);
            o.Hours = float.Parse(Hours);
            o.CAT_TimeSheet_Status_ID = Convert.ToInt32(CAT_TimeSheet_Status_ID);
            o.CAT_Work_Status_ID = Convert.ToInt32(CAT_Work_Status_ID);
            o.Percent_Complete = Convert.ToInt32(Percent_Complete);
            o.Description = Description;
            o.FeedBack = FeedBack;
            if (!string.IsNullOrEmpty(ApprovedBy)) o.ApprovedBy = Convert.ToInt32(ApprovedBy);
            if (!string.IsNullOrEmpty(ApproveOn)) o.ApproveOn = Convert.ToDateTime(ApproveOn);
            if (!string.IsNullOrEmpty(RejectedBy)) o.RejectedBy = Convert.ToInt32(RejectedBy);
            if (!string.IsNullOrEmpty(RejectedOn)) o.RejectedOn = Convert.ToDateTime(RejectedOn);
            if (isInsert)
            {
                db.ud_gSDMS_TIMESHEETs.InsertOnSubmit(o);
            }
            db.SubmitChanges();
            TimeSheet_ID = o.TimeSheet_ID.ToString();
        }

        #endregion

        #region ud_gSDMS_CAT_PROJECT_TEST_STATUS

        static public List<ud_gSDMS_CAT_PROJECT_TEST_STATUS> ud_gSDMS_CAT_PROJECT_TEST_STATUS_Lst()
        {
            var db = new gSDMS_DataLinq();
            var status = from c in db.ud_gSDMS_CAT_PROJECT_TEST_STATUS
                         where c.Active == true
                         select c;

            return status.ToList();
        }

        static public DataTable ud_gSDMS_CAT_PROJECT_TEST_STATUS_Get(string name)
        {
            var db = new gSDMS_DataLinq();
            var status = from c in db.ud_gSDMS_CAT_PROJECT_TEST_STATUS
                         where c.Active == true && c.Name == name
                         select c;

            return Library.LINQToDataTable(db, status);
        }
        #endregion

        #region "Insert, update Timesheet......"
        public static void ud_gSDMS_TIMESHEET_Upd(ref string TimeSheet_ID, string Project_ID, string CAT_Project_Step_ID, string Account,
                                                 string Date, string From, string To, string Hours,
                                                 string CAT_TimeSheet_Status_ID, string CAT_Work_Status_ID,
                                                 string Percent_Complete, string Description, string FeedBack,
                                                 string ApprovedBy, string ApproveOn, string RejectedBy,
                                                 string RejectedOn)
        {
            bool isInsert = string.IsNullOrEmpty(TimeSheet_ID);
            var db = new gSDMS_DataLinq();
            ud_gSDMS_TIMESHEET o;
            if (isInsert)
            {
                o = new ud_gSDMS_TIMESHEET();
            }
            else
            {
                int intTimeSheet_ID = Convert.ToInt32(TimeSheet_ID);
                o = db.ud_gSDMS_TIMESHEETs.Where(j => j.TimeSheet_ID == intTimeSheet_ID).SingleOrDefault();
            }
            //o.TimeSheet_ID = Convert.ToInt32(TimeSheet_ID);
            o.Project_ID = Convert.ToInt32(Project_ID);
            o.CAT_Project_Step_ID = Convert.ToInt32(CAT_Project_Step_ID);
            if (!string.IsNullOrEmpty(Date)) o.Date = Convert.ToDateTime(Date);
            o.From = Math.Round(Convert.ToDouble(From), 1);
            o.To = Math.Round(Convert.ToDouble(To), 1);
            o.Hours = Math.Round(Convert.ToDouble(Hours), 1);
            o.CAT_TimeSheet_Status_ID = Convert.ToInt32(CAT_TimeSheet_Status_ID);
            o.CAT_Work_Status_ID = Convert.ToInt32(CAT_Work_Status_ID);
            o.Percent_Complete = Convert.ToInt32(Percent_Complete);
            o.Description = Description;
            o.FeedBack = FeedBack;
            if (!string.IsNullOrEmpty(Account)) o.Account = Convert.ToInt32(Account);
            if (!string.IsNullOrEmpty(ApprovedBy)) o.ApprovedBy = Convert.ToInt32(ApprovedBy);
            if (!string.IsNullOrEmpty(ApproveOn)) o.ApproveOn = Convert.ToDateTime(ApproveOn);
            if (!string.IsNullOrEmpty(RejectedBy)) o.RejectedBy = Convert.ToInt32(RejectedBy);
            if (!string.IsNullOrEmpty(RejectedOn)) o.RejectedOn = Convert.ToDateTime(RejectedOn);
            if (isInsert)
            {
                db.ud_gSDMS_TIMESHEETs.InsertOnSubmit(o);
            }
            db.SubmitChanges();
            TimeSheet_ID = o.TimeSheet_ID.ToString();
        }
        #endregion
        #endregion

        #region ud_gSDMS_PROJECT_TEST_ERROR
        static public DataTable ud_gSDMS_PROJECT_TEST_ERROR_Lst(int Project_Test_ID)
        {
            var db = new gSDMS_DataLinq();
            var temp = from c in db.ud_gSDMS_PROJECT_TEST_ERRORs
                       where c.Project_Test_ID == Project_Test_ID
                       select new
                       {
                           Test_Error_ID = c.Test_Error_ID,
                           Error_Code = c.Test_Error_Code,
                           Status = c.ud_gSDMS_CAT_ERROR_STATUS.Name,
                           Sender = c.mp_User.LoginName,
                           Assigned_To = c.mp_User1.LoginName,
                           Create_Date = c.Created_On,
                           Descripton = c.Description

                       };

            return Library.LINQToDataTable(db, temp);
        }

        static public DataTable ud_gSDMS_PROJECT_TEST_ERROR_Lst_Except(int Project_Test_ID, string status)
        {
            var db = new gSDMS_DataLinq();
            var temp = from c in db.ud_gSDMS_PROJECT_TEST_ERRORs
                       where c.Project_Test_ID == Project_Test_ID
                       && c.ud_gSDMS_CAT_ERROR_STATUS.Name != status
                       select new
                       {
                           Test_Error_ID = c.Test_Error_ID,
                           Error_Code = c.Test_Error_Code,
                           Status = c.ud_gSDMS_CAT_ERROR_STATUS.Name,
                           Sender = c.mp_User.LoginName,
                           Assigned_To = c.mp_User1.LoginName,
                           Create_Date = c.Created_On,
                           Descripton = c.Description

                       };

            return Library.LINQToDataTable(db, temp);
        }

        static public DataTable ud_gSDMS_PROJECT_TEST_ERROR_Lst(int Project_Test_ID, string status)
        {
            var db = new gSDMS_DataLinq();
            var temp = from c in db.ud_gSDMS_PROJECT_TEST_ERRORs
                       where c.Project_Test_ID == Project_Test_ID
                       && c.ud_gSDMS_CAT_ERROR_STATUS.Name == status
                       select new
                       {
                           Test_Error_ID = c.Test_Error_ID,
                           Error_Code = c.Test_Error_Code,
                           Status = c.ud_gSDMS_CAT_ERROR_STATUS.Name,
                           Sender = c.mp_User.LoginName,
                           Assigned_To = c.mp_User1.LoginName,
                           Create_Date = c.Created_On,
                           Descripton = c.Description

                       };

            return Library.LINQToDataTable(db, temp);
        }

        static public DataTable ud_gSDMS_PROJECT_TEST_ERROR_Get(int Test_Error_ID)
        {
            var db = new gSDMS_DataLinq();
            var error = (from c in db.ud_gSDMS_PROJECT_TEST_ERRORs
                         where c.Test_Error_ID == Test_Error_ID
                         select new
                         {
                             c.Test_Error_ID,
                             c.Test_Error_Code,
                             c.Project_Test_ID,
                             c.ud_gSDMS_PROJECT_TEST.ud_gSDMS_PROJECT.Code,
                             c.Title,
                             c.Description,
                             c.Feedback,
                             c.ud_gSDMS_PROJECT_TEST.ud_gSDMS_PROJECT.Project_Leader,
                             c.ud_gSDMS_PROJECT_TEST.ud_gSDMS_PROJECT.Project_ID,
                             c.Created_On,
                             c.Created_By,
                             c.CAT_Error_Status_ID

                         });
            if (error != null)
                return Library.LINQToDataTable(db, error);
            else
                return null;
        }

        #region ud_gSDMS_CAT_PROJECT_TEST_ERROR_upd
        static public void ud_gSDMS_CAT_PROJECT_TEST_ERROR_upd(int pid, string pfeed, DateTime? pxdate, int pstatus)
        {
            var db = new gSDMS_DataLinq();

            ud_gSDMS_PROJECT_TEST_ERROR o = db.ud_gSDMS_PROJECT_TEST_ERRORs.Single(p => p.Test_Error_ID == pid);

            o.Feedback = pfeed;
            o.Fixed_On = pxdate;
            o.CAT_Error_Status_ID = pstatus;

            db.SubmitChanges();

        }
        #endregion

        #region ud_gSDMS_CAT_PROJECT_TEST_ERROR_Clo
        static public void ud_gSDMS_CAT_PROJECT_TEST_ERROR_Clo(int pid, int pstatus)
        {
            var db = new gSDMS_DataLinq();

            ud_gSDMS_PROJECT_TEST_ERROR o = db.ud_gSDMS_PROJECT_TEST_ERRORs.Single(p => p.Test_Error_ID == pid);

            o.CAT_Error_Status_ID = pstatus;

            db.SubmitChanges();

        }
        #endregion

        #region ud_gSDMS_CAT_PROJECT_TEST_ERROR_insert
        static public void ud_gSDMS_CAT_PROJECT_TEST_ERROR_insert(int projectCodeID, string ProjectCode, string projectTitle, string description, int projectFileID, int createBy, DateTime? createOn, int fixBy, int status)
        {
            var db = new gSDMS_DataLinq();

            ud_gSDMS_PROJECT_TEST_ERROR o = new ud_gSDMS_PROJECT_TEST_ERROR();

            o.Test_Error_Code = ProjectCode;
            o.Project_Test_ID = projectCodeID;
            o.Title = projectTitle;
            o.Description = description;
            o.File_Attach_ID = projectFileID;
            o.Created_By = createBy;
            o.Created_On = createOn;
            o.Fixed_By = fixBy;
            o.CAT_Error_Status_ID = status;

            db.ud_gSDMS_PROJECT_TEST_ERRORs.InsertOnSubmit(o);
            db.SubmitChanges();

        }
        #endregion

        #region ud_gSDMS_CAT_PROJECT_TEST_ERROR_CODE_AutoGenCode
        static public string ud_gSDMS_CAT_PROJECT_TEST_ERROR_CODE_AutoGenCode(string Project_Test_ID, string Project_Code)
        {
            var db = new gSDMS_DataLinq();
            if (string.IsNullOrEmpty(Project_Test_ID)) return null;


            var Error = (from j in db.ud_gSDMS_PROJECT_TEST_ERRORs
                         where j.Project_Test_ID == Convert.ToInt32(Project_Test_ID)
                         orderby j.Created_On descending
                         select j).Take(1).SingleOrDefault();
            var code = "";

            if (Error != null)
            {
                string newStr = Error.Test_Error_Code.Trim();
                string cStr = newStr.Substring(newStr.Length - 3, 3);
                string Count = (Convert.ToInt32(cStr) + 1).ToString();
                string codeCount = Count.PadLeft(3, '0');
                code = string.Format("{0}_ERROR_{1}", Project_Code, codeCount);
            }
            else
            {
                code = string.Format("{0}_ERROR_{1}", Project_Code, "001");
            }
            return code;
        }
        #endregion

        #region ud_gSDMS_PROJECT_TEST_Del
        public static bool ud_gSDMS_PROJECT_TEST_Del(int Project_Test_ID)
        {
            var db = new gSDMS_DataLinq();
            if (Project_Test_ID < 1) return false;
            var o = db.ud_gSDMS_PROJECT_TESTs.Where(j => j.Project_Test_ID == Project_Test_ID).SingleOrDefault();

            if (o != null)
            {

                var errors = (from c in db.ud_gSDMS_PROJECT_TEST_ERRORs
                              where c.Project_Test_ID == Project_Test_ID
                              select c).ToList();

                foreach (var p in errors)
                    ud_gSDMS_PROJECT_TEST_ERROR_Del(p.Test_Error_ID);


                db.ud_gSDMS_PROJECT_TESTs.DeleteOnSubmit(o);
                db.SubmitChanges();
            }
            else return false;

            return true;
        }
        #endregion

        #region ud_gSDMS_PROJECT_TEST_ERROR_Del
        public static bool ud_gSDMS_PROJECT_TEST_ERROR_Del(int Test_Error_ID)
        {

            var db = new gSDMS_DataLinq();

            var o = db.ud_gSDMS_PROJECT_TEST_ERRORs.Where(j => j.Test_Error_ID == Test_Error_ID).SingleOrDefault();

            if (o != null)
            {
                db.ud_gSDMS_PROJECT_TEST_ERRORs.DeleteOnSubmit(o);
                db.SubmitChanges();
            }
            else return false;

            return true;
        }

        public static bool ud_gSDMS_PROJECT_TEST_ERROR_Del(ud_gSDMS_PROJECT_TEST_ERROR error)
        {

            var db = new gSDMS_DataLinq();

            if (error != null)
            {
                db.ud_gSDMS_PROJECT_TEST_ERRORs.DeleteOnSubmit(error);
                db.SubmitChanges();
            }
            else return false;

            return true;
        }
        #endregion

        static public void ud_gSDMS_CAT_PROJECT_TEST_ERROR_Clo(int pid, string content, int pstatus)
        {
            var db = new gSDMS_DataLinq();

            ud_gSDMS_PROJECT_TEST_ERROR o = db.ud_gSDMS_PROJECT_TEST_ERRORs.Single(p => p.Test_Error_ID == pid);

            o.Description = content;
            o.CAT_Error_Status_ID = pstatus;

            db.SubmitChanges();

        }
        #endregion



        #region ud_gSDMS_PROJECT_TEST
        static public DataTable ud_gSDMS_PROJECT_TEST_Search(string projectCode, string projectName, string fromDate, string toDate, string sender, string assignedTo, string status)
        {
            var db = new gSDMS_DataLinq();

           

            var testProjects = from c in db.ud_gSDMS_PROJECT_TESTs
                               where ((string.IsNullOrEmpty(projectCode) ? true : projectCode == c.ud_gSDMS_PROJECT.Project_ID.ToString())
                               && (string.IsNullOrEmpty(projectName) ? true : c.ud_gSDMS_PROJECT.Project_ID.ToString() == projectName))
                               && ((fromDate == null  ? true : Convert.ToDateTime(fromDate).Date <= c.CreateOn.Value.Date)
                               && (toDate == null ? true : c.CreateOn.Value.Date <= Convert.ToDateTime(toDate).Date))
                               && (string.IsNullOrEmpty(sender) ? true : c.mp_User.UserID.ToString() == sender)
                               && (string.IsNullOrEmpty(assignedTo) ? true : c.mp_User1.UserID.ToString() == assignedTo)
                               && (string.IsNullOrEmpty(status) ? true : c.CAT_Project_Test_Status_ID.ToString() == status)
                              
                               select new
                               {
                                   Project_Test_ID = c.Project_Test_ID,
                                   Project_Code = c.ud_gSDMS_PROJECT.Code,
                                   Project_Name = c.ud_gSDMS_PROJECT.Name,
                                   Status = c.ud_gSDMS_CAT_PROJECT_TEST_STATUS.Name,
                                   Total_Errors = (from d in c.ud_gSDMS_PROJECT_TEST_ERRORs
                                                   select d).Count(),
                                   Fixed = (from d in c.ud_gSDMS_PROJECT_TEST_ERRORs
                                            where d.ud_gSDMS_CAT_ERROR_STATUS.Name == "Closed"
                                            select d).Count(),
                                   Waiting_Approve = (from d in c.ud_gSDMS_PROJECT_TEST_ERRORs
                                                      where d.ud_gSDMS_CAT_ERROR_STATUS.Name == "Open"
                                                      select d).Count(),
                                   Pending = (from d in c.ud_gSDMS_PROJECT_TEST_ERRORs
                                              where d.ud_gSDMS_CAT_ERROR_STATUS.Name == "Pending"
                                              select d).Count(),
                                   Sender = c.mp_User.LoginName,
                                   Assigned_To = c.mp_User1.LoginName,
                                   c.Start_Date,
                                   c.End_Date,
                                   c.Project_ID

                               };
            return Library.LINQToDataTable(db, testProjects);
        }

        static public DataTable ud_gSDMS_PROJECT_LeaderGet(int projectID)
        {
            var db = new gSDMS_DataLinq();

            var temp = from c in db.ud_gSDMS_PROJECTs
                       join d in db.mp_Users on c.Project_Leader equals d.UserID
                       where c.Project_Leader == projectID
                       select new
                       {
                           c.Project_Leader,
                           d.LoginName
                       };

            return Library.LINQToDataTable(db, temp);
        }

        static public ud_gSDMS_PROJECT_TEST ud_gSDMS_PROJECT_TEST_Get(int Project_Test_ID)
        {
            var db = new gSDMS_DataLinq();
            var temp = (from c in db.ud_gSDMS_PROJECT_TESTs
                        where c.Project_Test_ID == Project_Test_ID
                        select c).FirstOrDefault();


            return temp;
        }

        #region ud_gSDMS_CAT_PROJECT_TEST_ASSIGN_TO
        static public DataTable ud_gSDMS_CAT_PROJECT_TEST_ASSIGN_TO(string para)
        {
            var db = new gSDMS_DataLinq();
            var userID = from c in db.mp_Roles
                         join d in db.mp_UserRoles on c.RoleID equals d.RoleID

                         select new { c.DisplayName, d.UserID };
            var rs = from e in db.mp_Users
                     join f in userID on e.UserID equals f.UserID
                     where f.DisplayName == para
                     select new { e.LoginName, e.UserID };

            return Library.LINQToDataTable(db, rs);
        }
        #endregion
        #endregion

        #region ud_gSDMS_PROJECT_PLAN
        static public DataTable ud_gSDMS_PROJECT_PLAN_Search(int Project_ID)
        {
            var db = new gSDMS_DataLinq();
            var project = (from c in db.ud_gSDMS_PROJECT_PLANs
                           where c.Project_ID == Project_ID
                           select c.mp_User);

            return Library.LINQToDataTable(db, project);
        }

        public static DataTable GetFeedBackProjectPlan(string projectid)
        {
            var db = new gSDMS_DataLinq();
            var query = from j in db.ud_gSDMS_PROJECT_PLANs
                        where j.Project_ID == Convert.ToInt32(projectid)
                        select j.FeedBack_Comment;
            return Library.LINQToDataTable(db, query);
        }

        #region "Insert, update ProjectPlan......"
        public static void ud_gSDMS_PROJECT_PLAN_upd(ref string Project_Plan_ID, string Project_ID, string CAT_Project_Step_ID, string UserID,
                                                 string Work_Description, string From_Date, string To_Date, string Days, string Hours,
                                                 string FeedBack_Comment, string CreatedBy, string CreatedOn)
        {
            bool isInsert = string.IsNullOrEmpty(Project_Plan_ID);
            var db = new gSDMS_DataLinq();
            ud_gSDMS_PROJECT_PLAN o;
            if (isInsert)
            {
                o = new ud_gSDMS_PROJECT_PLAN();
            }
            else
            {
                int intProjectPlan_ID = Convert.ToInt32(Project_Plan_ID);
                o = db.ud_gSDMS_PROJECT_PLANs.Where(j => j.Project_Plan_ID == intProjectPlan_ID).SingleOrDefault();
            }
            //o.TimeSheet_ID = Convert.ToInt32(TimeSheet_ID);
            if (!string.IsNullOrEmpty(Project_ID)) o.Project_ID = Convert.ToInt32(Project_ID);
            if (!string.IsNullOrEmpty(CAT_Project_Step_ID)) o.CAT_Project_Step_ID = Convert.ToInt32(CAT_Project_Step_ID);
            if (!string.IsNullOrEmpty(UserID)) o.UserID = Convert.ToInt32(UserID);
            if (!string.IsNullOrEmpty(From_Date)) o.From_Date = Convert.ToDateTime(From_Date);
            if (!string.IsNullOrEmpty(To_Date)) o.To_Date = Convert.ToDateTime(To_Date);
            o.Days = float.Parse(Days);
            o.Hours = float.Parse(Hours);
            if (!string.IsNullOrEmpty(FeedBack_Comment)) o.FeedBack_Comment = FeedBack_Comment;
            if (!string.IsNullOrEmpty(Work_Description)) o.Work_Description = Work_Description;
            if (!string.IsNullOrEmpty(CreatedBy)) o.CreatedBy = Convert.ToInt32(CreatedBy);
            if (!string.IsNullOrEmpty(CreatedOn)) o.CreatedOn = Convert.ToDateTime(CreatedOn);
            if (isInsert)
            {
                db.ud_gSDMS_PROJECT_PLANs.InsertOnSubmit(o);
            }
            db.SubmitChanges();
            Project_Plan_ID = o.Project_Plan_ID.ToString();
        }
        #endregion

        #region "Ham save load du lieu tu bang Project Plan len Gridview"
        public static DataTable ud_gSDMS_PROJECT_PLAN_Load(string project_id)
        {
            var db = new gSDMS_DataLinq();
            object data;
            int id = Convert.ToInt32(project_id);
            data = from t in db.ud_gSDMS_PROJECT_PLANs
                   where t.Project_ID == id
                   select new
                   {
                       t.Project_Plan_ID,
                       Project_ID = t.Project_ID,
                       CAT_Project_Step_ID = t.ud_gSDMS_CAT_PROJECT_STEP.Name,
                       UserID = t.mp_User.LoginName,
                       t.Work_Description,
                       t.From_Date,
                       t.To_Date,
                       t.Days,
                       t.Hours,
                       t.FeedBack_Comment,
                       t.CreatedBy,
                       t.CreatedOn
                   };

            return Library.LINQToDataTable(db, data);
        }

        static public List<ud_gSDMS_PROJECT_TEST> ud_gSDMS_PROJECT_TEST_LstArray()
        {
            var db = new gSDMS_DataLinq();
            var temp = (from c in db.ud_gSDMS_PROJECT_TESTs
                        select c).ToList();
            return temp;
        }

        static public List<ud_gSDMS_PROJECT> ud_gSDMS_PROJECT_PLAN_ProjectGet(int userID)
        {
            var db = new gSDMS_DataLinq();
            var temp = (from c in db.ud_gSDMS_PROJECT_PLANs
                        where c.UserID == userID
                        select c.ud_gSDMS_PROJECT
                       ).Distinct(); ;

            return temp.ToList();
        }

        static public List<ud_gSDMS_PROJECT_TEST> ud_gSDMS_PROJECT_TEST_Get_From_User(int userID)
        {
            var db = new gSDMS_DataLinq();
            var data = from c in db.ud_gSDMS_PROJECT_TESTs
                       where c.CreatedBy == userID || c.Assign_To == userID
                       select c;
            return data.ToList();
        }

        static public List<mp_User> mp_User_Lst(string role)
        {
            if (string.IsNullOrEmpty(role)) return null;

            var db = new gSDMS_DataLinq();
            var roleIDs = role.Split('|');

            var temp = (
                        from user in db.mp_Users
                        from userRole in db.mp_UserRoles
                        from Role in db.mp_Roles
                        where
                        (
                            (user.UserID == userRole.UserID) &&
                            (userRole.RoleID == Role.RoleID) &&
                            (roleIDs.Contains(Role.RoleID.ToString()))
                        )
                        select user).ToList();
            return temp;
        }

        static public bool mp_User_IsRole(int userID, string roleID)
        {

            List<int> ids = new List<int>();
            if (string.IsNullOrEmpty(roleID)) return false;
            else
            {
                var roles = roleID.Split("|".ToCharArray());
                foreach (var i in roles)
                    ids.Add(Convert.ToInt32(i));
            }
            var db = new gSDMS_DataLinq();
            var temp = (from c in db.mp_UserRoles
                        where c.UserID == userID
                        && ids.Contains(c.RoleID)
                        select c);
            return temp.Count() > 0;
        }

        static public bool ud_gSDMS_ProjectLeader(string projectid, string currentuserid)
        {
            int userid = Convert.ToInt32(currentuserid);
            if (string.IsNullOrEmpty(projectid)) return false;
            else
            {
                int project_id = Convert.ToInt32(projectid);
                var db = new gSDMS_DataLinq();
                var query = (from j in db.ud_gSDMS_PROJECTs
                             where (j.Project_ID == project_id) && (j.Project_Leader == userid) || (userid == 1)
                             select j);
                if (query.Count() > 0)
                    return true;
                else
                {
                    return false;
                }
            }
        }

        static public DataTable ud_gSDMS_PROJECT_TEST_SenderLst()
        {
            var db = new gSDMS_DataLinq();
            var temp = (from c in db.ud_gSDMS_PROJECT_TESTs
                        select new
                        {
                            Sender_ID = c.mp_User.UserID,
                            Sender_Name = c.mp_User.LoginName,
                        }).Distinct();

            return Library.LINQToDataTable(db, temp);
        }

        static public DataTable ud_gSDMS_PROJECT_TEST_Assign_ToLst()
        {
            var db = new gSDMS_DataLinq();
            var temp = (from c in db.ud_gSDMS_PROJECT_TESTs
                        select new
                        {
                            Assigned_To_ID = c.mp_User1.UserID,
                            Assigned_To_Name = c.mp_User1.LoginName,
                        }).Distinct();

            return Library.LINQToDataTable(db, temp);
        }
        #region ud_gSDMS_CAT_ERROR_STATUS

        static public DataTable ud_gSDMS_CAT_ERROR_STATUS_Lst()
        {
            var db = new gSDMS_DataLinq();
            var status = from c in db.ud_gSDMS_CAT_ERROR_STATUS
                         where c.Active == true
                         select c;

            return Library.LINQToDataTable(db, status);
        }

        static public DataTable ud_gSDMS_CAT_ERROR_STATUS_Get(string name)
        {
            var db = new gSDMS_DataLinq();
            var status = from c in db.ud_gSDMS_CAT_ERROR_STATUS
                         where c.Active == true && c.Name == name
                         select c;

            return Library.LINQToDataTable(db, status);
        }
        #endregion

        #region ud_gSDMS_PROJECT_TESTs

        static public ud_gSDMS_PROJECT_TEST ud_gSDMS_PROJECT_TESTs_Get(string Project_Test_ID)
        {
            int ID = string.IsNullOrEmpty(Project_Test_ID) ? -1 : Convert.ToInt32(Project_Test_ID);
            var db = new gSDMS_DataLinq();
            var project = db.ud_gSDMS_PROJECT_TESTs.Where(j => j.Project_Test_ID == ID).SingleOrDefault();
            return project;
        }


        static public ud_gSDMS_PROJECT_TEST ud_gSDMS_PROJECT_TESTs_Upd(int? Project_Test_ID, int Project_ID, DateTime? CreateOn, int CreatedBy, int Assign_To, DateTime? Start_Date, DateTime? End_Date, string Notes, int CAT_Project_Test_Status_ID, bool Active)
        {
            try
            {
                var db = new gSDMS_DataLinq();
                ud_gSDMS_PROJECT_TEST o = null; ;

                if (Project_Test_ID.HasValue)
                {
                    o = db.ud_gSDMS_PROJECT_TESTs.Where(j => j.Project_Test_ID == Project_Test_ID).SingleOrDefault();
                    o.CAT_Project_Test_Status_ID = CAT_Project_Test_Status_ID;
                    o.Notes = Notes;
                }
                else
                {


                    o = new ud_gSDMS_PROJECT_TEST();

                    o.Project_ID = Project_ID;
                    o.CreateOn = CreateOn;
                    o.CreatedBy = CreatedBy;
                    o.Assign_To = Assign_To;
                    o.Start_Date = Start_Date;
                    o.End_Date = End_Date;
                    o.Notes = Notes;
                    o.CAT_Project_Test_Status_ID = CAT_Project_Test_Status_ID;
                    o.Active = Active;


                    db.ud_gSDMS_PROJECT_TESTs.InsertOnSubmit(o);
                }

                db.ud_gSDMS_PROJECT_TESTs.Context.SubmitChanges();
                return o;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        static public DataTable ud_gSDMS_PROJECT_GetList()
        {
            var db = new gSDMS_DataLinq();
            var data = from j in db.ud_gSDMS_PROJECTs
                       select j;
            return Library.LINQToDataTable(db, data);
        }

        static public ud_gSDMS_PROJECT ud_gSDMS_PROJECT_Get(int? Project_ID)
        {
            var db = new gSDMS_DataLinq();
            var project = db.ud_gSDMS_PROJECTs.Where(j => j.Project_ID == Project_ID).SingleOrDefault();
            return project;
        }

        static public List<CustommerUserResult> mp_User_GetQA()
        {
            var db = new gSDMS_DataLinq();
            var QA = (
                        from user in db.mp_Users
                        from userRole in db.mp_UserRoles
                        from Role in db.mp_Roles
                        where
                        (
                            (user.UserID == userRole.UserID) &&
                            (userRole.RoleID == Role.RoleID) &&
                            (Role.DisplayName == "QA")
                        )
                        select new CustommerUserResult
                        {
                            UserID = user.UserID,
                            LoginName = user.LoginName
                        }
                    ).ToList<CustommerUserResult>();
            return QA;
            //return Library.LINQToDataTable(db, QA);
        }

        static public DataTable ud_gSDMS_PROJECT_GetUsers(int projectID)
        {
            var db = new gSDMS_DataLinq();

            var temp = from c in db.ud_gSDMS_PROJECT_PLANs
                       where c.Project_ID == projectID
                       select c.mp_User;

            return Library.LINQToDataTable(db, temp);
        }

        static public List<mp_User> ud_gSDMS_PROJECT_Member_Get_Lst(string projectID)
        {
            var db = new gSDMS_DataLinq();

            var temp = from c in db.ud_gSDMS_PROJECT_ROLEs
                       where string.IsNullOrEmpty(projectID) ? true : projectID.Split('|').Contains(c.Project_ID.ToString())
                       select c.mp_User;

            return temp.ToList();
        }

        static public DataTable ud_gSDMS_PROJECT_senderChk(int projectID, int senderID)
        {
            var db = new gSDMS_DataLinq();

            var temp = from c in db.ud_gSDMS_PROJECT_PLANs
                       where c.Project_ID == projectID
                                && c.UserID == senderID
                       select c;

            return Library.LINQToDataTable(db, temp);
        }
        public class CustommerUserResult
        {
            public int UserID
            {
                get;
                set;
            }
            public string LoginName
            {
                get;
                set;
            }
        }
        //

        static public void ud_gSDMS_CAT_PROJECT_TEST_ERROR_insert(int pproID, string ptcode, string ptitle, string pdescr, string pfeed, int pfile, int pcrtby, DateTime? pcrtdate, int pfxby, DateTime? pxdate, int pstatus)
        {
            var db = new gSDMS_DataLinq();

            ud_gSDMS_PROJECT_TEST_ERROR o = new ud_gSDMS_PROJECT_TEST_ERROR();

            o.Test_Error_Code = ptcode;
            o.Project_Test_ID = pproID;
            o.Title = ptitle;
            o.Description = pdescr;
            o.Feedback = pfeed;
            o.File_Attach_ID = pfile;
            o.Created_By = pcrtby;
            o.Created_On = pcrtdate;
            o.Fixed_By = pfxby;
            o.Fixed_On = pxdate;
            o.CAT_Error_Status_ID = pstatus;

            db.ud_gSDMS_PROJECT_TEST_ERRORs.InsertOnSubmit(o);
            db.SubmitChanges();

        }

        static public DataTable ud_gSDMS_PROJECT_TEST_Not_Test_Lst()
        {
            var db = new gSDMS_DataLinq();

            var project = (from c in db.ud_gSDMS_PROJECTs
                           from d in db.ud_gSDMS_PROJECT_TESTs
                           where (c.Project_ID != d.Project_ID)
                           select c).Distinct();
            return Library.LINQToDataTable(db, project);
        }

        static public DataTable ud_gSDMS_PROJECT_TEST_Not_Test_FromUser(int userID)
        {
            var db = new gSDMS_DataLinq();

            var projectTested = (
                           from e in db.ud_gSDMS_PROJECT_TESTs
                           select e.Project_ID).Distinct();

            var projectNotTest = (from c in db.ud_gSDMS_PROJECTs
                                  where projectTested.Contains(c.Project_ID) == false
                                  && (from d in c.ud_gSDMS_PROJECT_ROLEs
                                      where d.UserID == userID
                                      select d).Count() > 0
                                  select c);


            return Library.LINQToDataTable(db, projectNotTest);
        }

        static public DataTable ud_gSDMS_CAT_ERROR_STATUS_Get(string name1, string name2)
        {
            var db = new gSDMS_DataLinq();
            var status = from c in db.ud_gSDMS_CAT_ERROR_STATUS
                         where c.Active == true && (c.Name == name1
                         || c.Name == name2)
                         select c;

            return Library.LINQToDataTable(db, status);
        }
        #endregion

        #region "Insert, update ProjectPlan......"
        public static void ud_gSDMS_PROJECT_PLAN_updStatus(string Project_Plan_ID)
        {
            bool isInsert = string.IsNullOrEmpty(Project_Plan_ID);
            var db = new gSDMS_DataLinq();
            ud_gSDMS_PROJECT o;
            int intProjectPlan_ID = Convert.ToInt32(Project_Plan_ID);
            o = db.ud_gSDMS_PROJECTs.Where(j => j.CAT_Project_Plan_Status_ID == intProjectPlan_ID).SingleOrDefault();
            o.CAT_Project_Step_ID = 2;
            db.SubmitChanges();
        }
        #endregion

        #region "Ham upload khi edit lai du lieu khi chon Edit...."
        public static ud_gSDMS_PROJECT_PLAN ud_gSDMS_PROJECT_PLAN_UpLoad(string id)
        {
            int intId = string.IsNullOrEmpty(id) ? -1 : Convert.ToInt32(id);
            var db = new gSDMS_DataLinq();
            var data = db.ud_gSDMS_PROJECT_PLANs.Where(t => t.Project_Plan_ID == intId).SingleOrDefault();
            return data;
        }
        #endregion

        #region "Ham delete Project Plan.........."
        public static void ud_gSDMS_Project_Plan_Del(string id, bool isPermanent)
        {
            int intId = string.IsNullOrEmpty(id) ? -1 : Convert.ToInt32(id);
            var db = new gSDMS_DataLinq();
            var o = db.ud_gSDMS_PROJECT_PLANs.Where(j => j.Project_Plan_ID == intId).SingleOrDefault();
            if (o != null)
            {
                if (isPermanent)
                    db.ud_gSDMS_PROJECT_PLANs.DeleteOnSubmit(o);
                else
                {
                    //o.TimeSheet_ID = -1;
                }
                db.SubmitChanges();
            }
            return;
        }
        #endregion

        #region update lai CAT_Project_Plan_Status_ID = 'Approved' and CAT_Project_Step_ID = 'Design'
        public static void ud_gSDMS_PROJECT_UpdStatusAndStep(ref string Project_ID, string CAT_Project_Step_ID, string CAT_Project_Plan_Status_ID)
        {
            bool isInsert = string.IsNullOrEmpty(Project_ID);
            var db = new gSDMS_DataLinq();
            ud_gSDMS_PROJECT o;
            if (isInsert)
            {
                o = new ud_gSDMS_PROJECT();
            }
            else
            {
                var intProject_ID = Convert.ToInt32(Project_ID);
                o = db.ud_gSDMS_PROJECTs.Where(j => j.Project_ID == intProject_ID).SingleOrDefault();
            }
            if (!string.IsNullOrEmpty(CAT_Project_Step_ID))
                o.CAT_Project_Step_ID = Convert.ToInt32(CAT_Project_Step_ID);
            if (!string.IsNullOrEmpty(CAT_Project_Plan_Status_ID))
                o.CAT_Project_Plan_Status_ID = Convert.ToInt32(CAT_Project_Plan_Status_ID);
            if (isInsert)
            {
                db.ud_gSDMS_PROJECTs.InsertOnSubmit(o);
            }
            db.SubmitChanges();
            //Project_ID = o.Project_ID.ToString();
        }
        #endregion

        #region update lai CAT_Project_Plan_Status_ID = 'Replan' trong btnSend and FeedBack
        public static void ud_gSDMS_PROJECT_UpdStatusSendFeedBack(ref string Project_ID, string CAT_Project_Plan_Status_ID)
        {
            bool isInsert = string.IsNullOrEmpty(Project_ID);
            var db = new gSDMS_DataLinq();
            ud_gSDMS_PROJECT o;
            var intProject_ID = Convert.ToInt32(Project_ID);
            o = db.ud_gSDMS_PROJECTs.Where(j => j.Project_ID == intProject_ID).SingleOrDefault();
            if (!string.IsNullOrEmpty(CAT_Project_Plan_Status_ID))
                o.CAT_Project_Plan_Status_ID = Convert.ToInt32(CAT_Project_Plan_Status_ID);
            db.SubmitChanges();
            Project_ID = o.Project_ID.ToString();
        }
        #endregion
        #endregion


        #region mp_UserRole
        static public List<mp_UserRole> mp_UserRole_Search_RoleLst(int userRoleID, int roleID, int userID)
        {
            var db = new gSDMS_DataLinq();
            var temp = (from c in db.mp_UserRoles
                        where (userRoleID == 0 ? true : userRoleID == c.ID)
                        && (roleID == 0 ? true : roleID == c.RoleID)
                        && (userID == 0 ? true : userID == c.UserID)
                        select c).Distinct();

            return temp.ToList();
        }


        static public DataTable mp_Role_Search(string roleIDs, string roleNames, string displayNames)
        {
            var db = new gSDMS_DataLinq();

            List<string> idLst = new List<string>();
            List<string> nameLst = new List<string>();
            List<string> displayLst = new List<string>();

            if (!string.IsNullOrEmpty(roleIDs))
                idLst = roleIDs.Split("|".ToCharArray()).ToList();
            if (!string.IsNullOrEmpty(roleNames))
                nameLst = roleNames.Split("|".ToCharArray()).ToList();
            if (!string.IsNullOrEmpty(displayNames))
                displayLst = displayNames.Split("|".ToCharArray()).ToList();
            var temp = from c in db.mp_Roles
                       where (string.IsNullOrEmpty(roleIDs) ? true : idLst.Contains(c.RoleID.ToString()))
                       && (string.IsNullOrEmpty(roleNames) ? true : nameLst.Contains(c.RoleName))
                       && (string.IsNullOrEmpty(displayNames) ? true : displayLst.Contains(c.DisplayName))
                       select c;
            return Library.LINQToDataTable(db, temp);
        }


        #endregion

        #region ud_gSDMS_CAT_CONTRACT_STATUS
        static public List<ud_gSDMS_CAT_CONTRACT_STATUS> ud_gSDMS_CAT_CONTRACT_STATUS_Lst()
        {
            var db = new gSDMS_DataLinq();

            var temp = (from c in db.ud_gSDMS_CAT_CONTRACT_STATUS
                        select c).ToList();

            return temp;
        }

        static public ud_gSDMS_CAT_CONTRACT_STATUS ud_gSDMS_CAT_CONTRACT_STATUS_Get(int id, string name)
        {
            var db = new gSDMS_DataLinq();

            var temp = (from c in db.ud_gSDMS_CAT_CONTRACT_STATUS
                        where (id == 0 ? true : c.CAT_Contract_Status_ID == id)
                        && (name == "" ? true : c.Name == name)
                        select c).FirstOrDefault();

            return temp;
        }

        static public void ud_gSDMS_CAT_PROJECT_TEST_ERROR_insert(int projectCodeID, string ProjectCode, string projectTitle, string description, int projectFileID, int createBy, DateTime? createOn, int fixBy, int status, ref int errorID)
        {
            var db = new gSDMS_DataLinq();

            ud_gSDMS_PROJECT_TEST_ERROR o = new ud_gSDMS_PROJECT_TEST_ERROR();

            o.Test_Error_Code = ProjectCode;
            o.Project_Test_ID = projectCodeID;
            o.Title = projectTitle;
            o.Description = description;
            o.File_Attach_ID = projectFileID;
            o.Created_By = createBy;
            o.Created_On = createOn;
            o.Fixed_By = fixBy;
            o.CAT_Error_Status_ID = status;

            db.ud_gSDMS_PROJECT_TEST_ERRORs.InsertOnSubmit(o);
            db.SubmitChanges();

            errorID = o.Test_Error_ID;
        }

        static public void ud_gSDMS_CAT_PROJECT_TEST_ERROR_upd(int pid, int fileID)
        {
            var db = new gSDMS_DataLinq();

            ud_gSDMS_PROJECT_TEST_ERROR o = db.ud_gSDMS_PROJECT_TEST_ERRORs.Single(p => p.Test_Error_ID == pid);

            o.File_Attach_ID = fileID;

            db.SubmitChanges();

        }
        #endregion     

        #region ud_gSDMS_CAT_PRODUCT_PACKAGE
        public static DataTable ud_gSDMS_CAT_PRODUCT_PACKAGE_Search(string id, string code, string name , bool active)
        {
            var db = new gSDMS_DataLinq();

            var data = from c in db.ud_gSDMS_CAT_PRODUCT_PACKAGEs
                       where (string.IsNullOrEmpty(id) ? true : c.CAT_Product_Package_ID.ToString().Equals(id))
                       && (string.IsNullOrEmpty(code) ? true : c.Code.Equals(code))
                       && (string.IsNullOrEmpty(name) ? true : c.Name.ToLower().Contains(name.ToLower()))
                       && (c.Active.Equals(active))
                       select c;

            return Library.LINQToDataTable(db , data);
        }

        public static void ud_gSDMS_CAT_PRODUCT_PACKAGE_Del(string id, bool deActive)
        {
            var db = new gSDMS_DataLinq();

            var o = db.ud_gSDMS_CAT_PRODUCT_PACKAGEs.Single(p => p.CAT_Product_Package_ID.ToString().Equals(id));

            if (deActive)
            {
                o.Active = false;
            }
            else
            {
                db.ud_gSDMS_CAT_PRODUCT_PACKAGEs.DeleteOnSubmit(o);
            }
            db.SubmitChanges();
        }

        public static void ud_gSDMS_CAT_PRODUCT_PACKAGE_Upd(ref string id, string code, string name, string description, bool active)
        {
            var db = new gSDMS_DataLinq();

            bool isInsert = string.IsNullOrEmpty(id);
            ud_gSDMS_CAT_PRODUCT_PACKAGE o;
            if (isInsert)
            {
                o = new ud_gSDMS_CAT_PRODUCT_PACKAGE();
            }
            else
            {
                int _id = Convert.ToInt32(id);
                o = db.ud_gSDMS_CAT_PRODUCT_PACKAGEs.Single(p => p.CAT_Product_Package_ID == _id);
            }

            if (!string.IsNullOrEmpty(code)) o.Code = code;
            if (!string.IsNullOrEmpty(name)) o.Name = name;
            if (!string.IsNullOrEmpty(description)) o.Description = description;
            o.Active = active;

            if (isInsert)
            {
                db.ud_gSDMS_CAT_PRODUCT_PACKAGEs.InsertOnSubmit(o);
            }

            db.SubmitChanges();
            id = o.CAT_Product_Package_ID.ToString();
        }

        public static ud_gSDMS_CAT_PRODUCT_PACKAGE ud_gSDMS_CAT_PRODUCT_PACKAGE_Get(string id)
        {
            if (string.IsNullOrEmpty(id)) return null;

            var db = new gSDMS_DataLinq();
            var data = (from c in db.ud_gSDMS_CAT_PRODUCT_PACKAGEs
                        where c.CAT_Product_Package_ID.ToString().Equals(id)
                        select c).FirstOrDefault();

            return data;
        }

        #endregion
    }

        #region "Class Attach File, Upload, download file......"
    public class ud_gSDMS_Attachments
    {
        readonly gSDMS_DataLinq db = new gSDMS_DataLinq();
        public ud_gSDMS_Attachment GetOne(int fileID)
        {
            var result = from p in db.ud_gSDMS_Attachments
                         where p.FileID.Equals(fileID)
                         select p;
            return result.FirstOrDefault();
        }

        public object GetOneObj(int fileID)
        {
            var result = from p in db.ud_gSDMS_Attachments
                         where p.FileID.Equals(fileID)
                         select new { p };
            return result.ToList();
        }

        public List<ud_gSDMS_Attachment> GetAll(string serverFileName)
        {
            var result = from p in db.ud_gSDMS_Attachments
                         where p.ServerFileName == serverFileName
                         orderby p.LastModified descending
                         select p;
            return result.ToList();
        }

        public void IncreaseDownloadCount(int fileID)
        {
            var result = GetOne(fileID);
            result.DownloadCount++;
            db.SubmitChanges();
        }

        public object GetByItemID(string OptionForm, string OptionForm_Id)
        {
            var result = from p in db.ud_gSDMS_Attachments
                         where p.OptionForm.Equals(OptionForm) && p.OptionForm_Id.Equals(OptionForm_Id)
                         select new { p };
            return result.ToList();
        }

        public List<ud_gSDMS_Attachment> GetByItemID_Lst(string OptionForm, string OptionForm_Id)
        {
            var result = from p in db.ud_gSDMS_Attachments
                         where p.OptionForm.Equals(OptionForm) && p.OptionForm_Id.Equals(OptionForm_Id)
                         select p;
            return result.ToList();
        }

        public bool Delete(int fileID)
        {
            bool flag = false;
            ud_gSDMS_Attachment item = GetOne(fileID);
            db.ud_gSDMS_Attachments.DeleteOnSubmit(item);
            try
            {
                db.SubmitChanges();
                flag = true;
            }
            catch
            { }
            return flag;
        }

        public bool Delete(string serverFileName)
        {
            bool flag = false;
            List<ud_gSDMS_Attachment> listItem = GetAll(serverFileName);
            db.ud_gSDMS_Attachments.DeleteAllOnSubmit(listItem);
            try
            {
                db.SubmitChanges();
                flag = true;
            }
            catch
            { }
            return flag;
        }

        public bool Create(string fileName, string serverFileName, int sizeInKB, out int fileID, string OptionForm, string OptionForm_Id)
        {
            var flag = false;
            var item = new ud_gSDMS_Attachment
            {
                FileName = fileName,
                ServerFileName = serverFileName,
                SizeInKB = sizeInKB,
                DownloadCount = 0,
                LastModified = DateTime.UtcNow,
                OptionForm = OptionForm,
                OptionForm_Id = OptionForm_Id
            };
            db.ud_gSDMS_Attachments.InsertOnSubmit(item);
            try
            {
                db.SubmitChanges();
                flag = true;
            }
            catch { }
            fileID = item.FileID;
            return flag;
        }

        public bool CreateItemAttachment(int moduleID, int itemID, int fileID)
        {
            bool flag = false;

            //ud_gSDMS_Attachment item = new ud_gSDMS_Attachment
            //{
            //    ModuleID = moduleID,
            //    ItemID = itemID,
            //    FileID = fileID
            //};
            //db.ud_gSDMS_Attachments.InsertOnSubmit(item);
            //try
            //{
            //    db.SubmitChanges();
            //    flag = true;
            //}
            //catch { }
            return flag;
        }
        public bool UpdateItemAttachment(int ID, string itemID, int fileID)
        {
            bool flag = false;

            //ud_gSDMS_Attachment item = GetOneItemAttachment(ID);
            //if (item != null)
            //{
            //    item.OptionForm_Id = itemID;
            //    item.FileID = fileID;
            //    try
            //    {
            //        db.SubmitChanges();
            //        flag = true;
            //    }
            //    catch { }
            //}
            return flag;
        }
        public bool Update(int categoryID, string fileName, string serverFileName, int sizeInKB)
        {
            bool flag = false;

            ud_gSDMS_Attachment item = GetOne(categoryID);
            if (item != null)
            {
                item.FileName = fileName;
                item.ServerFileName = serverFileName;
                item.SizeInKB = sizeInKB;
                item.LastModified = DateTime.UtcNow;
                try
                {
                    db.SubmitChanges();
                    flag = true;
                }
                catch { }
            }
            return flag;
        }
        public void DeleteByModule(int moduleID)
        {
            //var result = from p in db.ud_gSDMS_Attachments
            //             where p.ModuleID.Equals(moduleID)
            //             select p;
            //db.ud_gSDMS_Attachments.DeleteAllOnSubmit(result);
            //var resultItemAttachment = from p in db.ud_gSDMS_Attachments
            //                           where p.ModuleID.Equals(moduleID)
            //                           select p;
            //db.ud_gSDMS_Attachments.DeleteAllOnSubmit(result);
            //db.ud_gSDMS_Attachments.DeleteAllOnSubmit(resultItemAttachment);
            //db.SubmitChanges();
        }

        public ud_gSDMS_Attachment GetOneItemAttachment(int ID)
        {
            var result = from p in db.ud_gSDMS_Attachments
                         where p.FileID.Equals(ID)
                         select p;
            return result.FirstOrDefault();
        }

        public List<ud_gSDMS_Attachment> GetAllItemAttachment(int OptionForm_Id)
        {
            var result = from p in db.ud_gSDMS_Attachments
                         where p.OptionForm_Id.Equals(OptionForm_Id)
                         select p;
            return result.ToList();
        }

        public List<ud_gSDMS_Attachment> GetAllItemAttachment(string serverFileName)
        {
            var result = from p in db.ud_gSDMS_Attachments
                         join q in db.ud_gSDMS_Attachments on p.FileID equals q.FileID
                         where q.ServerFileName == serverFileName
                         select p;
            return result.ToList();
        }

        public List<ud_gSDMS_Attachment> GetAllItemAttachmentByItemID(int itemID)
        {
            var result = from p in db.ud_gSDMS_Attachments
                         where p.OptionForm_Id.Equals(itemID)
                         select p;
            return result.ToList();
        }

        public List<ud_gSDMS_Attachment> GetAllItemAttachmentByFileID(int fileID)
        {
            var result = from p in db.ud_gSDMS_Attachments
                         where p.FileID.Equals(fileID)
                         select p;
            return result.ToList();
        }

        public bool DeleteItemAttachment(int ID)
        {
            bool flag = false;
            ud_gSDMS_Attachment item = GetOneItemAttachment(ID);
            db.ud_gSDMS_Attachments.DeleteOnSubmit(item);
            try
            {
                db.SubmitChanges();
                flag = true;
            }
            catch
            { }
            return flag;
        }

        public bool DeleteItemAttachment(string serverFileName)
        {
            bool flag = false;
            List<ud_gSDMS_Attachment> listItem = GetAllItemAttachment(serverFileName);
            db.ud_gSDMS_Attachments.DeleteAllOnSubmit(listItem);
            try
            {
                db.SubmitChanges();
                flag = true;
            }
            catch
            { }
            return flag;
        }

        public bool DeleteItemAttachmentByItemID(int itemID)
        {
            bool flag = false;
            List<ud_gSDMS_Attachment> listItem = GetAllItemAttachmentByItemID(itemID);
            List<ud_gSDMS_Attachment> listFile = new List<ud_gSDMS_Attachment>();
            foreach (var udArticleItemAttachment in listItem)
            {
                listFile.Add(GetOne(udArticleItemAttachment.FileID));
            }
            db.ud_gSDMS_Attachments.DeleteAllOnSubmit(listFile);
            db.ud_gSDMS_Attachments.DeleteAllOnSubmit(listItem);
            try
            {
                db.SubmitChanges();
                flag = true;
            }
            catch
            { }
            return flag;
        }
    }
    #endregion
}